Convert a table with a range of integer values, into a new table with a row for each value

[snph1777]

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 ?

[OwenAuger · Accepted solution]

Hi @snph1777 

Here are some suggestions:

DAX version:

Note that GENERATE iterates over the rows of Couriers, so the 2nd argument of GENERATE is evaluated in the row context of Couriers and can refer directly to columns of Couriers without aggregation.

Couriers_FlattenedData =
SELECTCOLUMNS (
    GENERATE (
        Couriers,
        GENERATESERIES ( Couriers[MinWeight] + 1, Couriers[MaxWeight] )
    ),
    "Courier", Couriers[Courier],
    "Country", Couriers[Country],
    "Weight", [Value],
    "Freight", Couriers[Freight]
)

Power Query version:

Note: I used Table.CombineColumns as it nicely removes the columns that are being combined and replaces them with the new column. You could also use Table.AddColumn, then remove MinWeight/MaxWeight after that.

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

Regards,

Owen

[OwenAuger · Accepted solution]

Hi @snph1777 

Here are some suggestions:

DAX version:

Note that GENERATE iterates over the rows of Couriers, so the 2nd argument of GENERATE is evaluated in the row context of Couriers and can refer directly to columns of Couriers without aggregation.

Couriers_FlattenedData =
SELECTCOLUMNS (
    GENERATE (
        Couriers,
        GENERATESERIES ( Couriers[MinWeight] + 1, Couriers[MaxWeight] )
    ),
    "Courier", Couriers[Courier],
    "Country", Couriers[Country],
    "Weight", [Value],
    "Freight", Couriers[Freight]
)

Power Query version:

Note: I used Table.CombineColumns as it nicely removes the columns that are being combined and replaces them with the new column. You could also use Table.AddColumn, then remove MinWeight/MaxWeight after that.

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

Regards,

Owen

[snph1777]

@OwenAuger 

Hello Owen, Thanks for your help. Can you help me develop the table in DAX and Query Editor, using an increment value of 0.01, instead of 1 as I have asked in my original question ?

For example, for FedEx Austrlia, instead of weight 1 to 5 with a freight value of 3, I need weight values, 1.01, 1.02, 1.03....2.00....5.00, all with a freight value of 3. I tried to change the GENERATESERIES with an increment value of 0.01, but am not getting the desired output. Perhaps becasue the GENERATESERIES is inside  GENERATE and SELECTCOLUMNS

[OwenAuger · Accepted solution]

You're welcome 🙂
Sure thing, we can change the increment to 0.01.

The structure of the code is pretty similar. I have attached a PBIX for reference.

DAX version:

With the DAX version, we need to add a third argument to GENERATESERIES. Make sure the Weight column is set as a "Decimal number" type and formatted to display the decimal places.

Couriers_FlattenedData = 
VAR Increment = 0.01
RETURN
    SELECTCOLUMNS (
        GENERATE (
            Couriers,
            GENERATESERIES ( Couriers[MinWeight] + Increment, Couriers[MaxWeight], Increment )
        ),
        "Courier", Couriers[Courier],
        "Country", Couriers[Country],
        "Weight", [Value],
        "Freight", Couriers[Freight]
    )

Power Query version:

With the Power Query version, we now need to use List.Numbers to create the list with an increment other than 1, and also ensure that the Weight column is type number.

let
    Increment = 0.01,
    Source = Couriers,
    WeightList =
      Table.CombineColumns( Source, {"MinWeight", "MaxWeight"},
        each let MinWeight = _{0}, MaxWeight = _{1}, Count = (MaxWeight-MinWeight)/Increment
        in List.Numbers(MinWeight+Increment, Count, Increment),
        "Weight"
      ),
    ExpandWeightList = Table.ExpandListColumn(WeightList, "Weight"),
    ChangedType = Table.TransformColumnTypes(ExpandWeightList,{{"Weight", type number}})
in
    ChangedType

 Regards,

Owen

[snph1777]

@OwenAuger    Thanks very much. Appreciate your help for my second question. I realize after writing the DAX formula to generate the table, I need to fix the decimal to 2 values. 

[snph1777]

Thanks Owen. I will use it and get back, and mark your answer. Appreciate your quick response.

[snph1777]

For the Query Editor solution, I have duplicated the base table (Couriers) inside the query editor, and then pasted your M-code in the Advanced Editor. And have later renamed that table as Couriers_FlattenedData.

I do not know anything in M-language , am grateful for your solution.

[snph1777]

@Anonymous Auger  Thanks Owen, appreciate your help greatly.