Microsoft Power BI DAX - Convert a table with a range of integer values in two columns, into a new table with a row for each value in the range

Question

I have a situation in which I am using Microsoft Power BI. I have a source table (called Couriers), with a range of weights (MinWeight to MaxWeight) for any given combination of Courier and Country, along with the Freight value.

I need to develop a new TABLE (called Couriers_FlattenedData) in Power BI , in which, I get a row for each value between the MinWeight and MaxWeight.

For example, if the minimum weight to maximum weight reads as 0 and 5 for FedEx Australia, I need 5 rows from 1 to 5.

I need these 4 columns in the new Couriers_FlattenedData table - Courier, Country, Weight, Freight. The Weight column is converted to rows based on the range in the source table.

I am trying to derive the new table, in both DAX as well as using the backend Power Query Editor (using M language). I would like to get both ways to develop this new table.

I tried something like this in DAX, but not able to get a solution.

Couriers_FlattenedData = SELECTCOLUMNS (
    
                                         GENERATE (

                                                    'Couriers',  GENERATESERIES (

                                                                                CALCULATE(DISTINCT(Couriers[MinWeight])+1),

                                                                                CALCULATE(DISTINCT(Couriers[MaxWeight]))

                                                                              )

                                                  ),

                                         "Courier", Couriers[Courier],

                                         "Country", Couriers[Country],

                                         "Freight", Couriers[Freight]

                                        )

Can someone correct the above DAX expression, which misses the Weight column ? Or even provide a solution using variables?

And also a step by step solution using the Power Query Editor of Power BI ?

user3812887 user3812887 · Accepted Answer · 2021-05-03T01:27:53

DAX Solution:

Couriers_FlattenedData = SELECTCOLUMNS (

                                         GENERATE (
                                                     Couriers,

                                                     GENERATESERIES(Couriers[MinWeight] + 1, Couriers[MaxWeight])

                                                  ),

                                         "Courier", Couriers[Courier],

                                         "Country", Couriers[Country],

                                         "Weight", [Value],

                                         "Freight", Couriers[Freight]

                                        )

Query Editor solution:

Duplicate the Couriers table (in the Query Editor), then go to Advanced Editor of the Query Editor, then paste this:

let
    Source = Couriers,
    WeightList = Table.CombineColumns(Source,{"MinWeight", "MaxWeight"},each {_{0}+1.._{1}},"Weight"),
    ExpandWeightList = Table.ExpandListColumn(WeightList, "Weight"),
    ChangedType = Table.TransformColumnTypes(ExpandWeightList,{{"Weight", Int64.Type}})
in
    ChangedType

Rename the table as Couriers_FlattenedData

Microsoft Power BI DAX - Convert a table with a range of integer values in two columns, into a new table with a row for each value in the range

1 Answers