Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply

Power Query Help

I have a table (sample below) where the "Spend Year" columns can vary in number.  I need to TRANSFORM these "Spend Year" columns by dividing them by the value in the "Count" column.  This needs to be dynamic so that it the number of "Spend Year" columns does not matter.

 

I was able to find a way to ADD A COLUMN:

 

Table.AddColumn(#"Previous Step","New Column", each List.Sum(Record.ToList(Record.SelectFields(_,List.RemoveItems(Table.ColumnNames(#"Changed Type"), {"Account", "Billed Month", "Count"}))))/[Count])

But I want the value returned by the division to stay in the respsective column for "Spend Year".  Using Table.TransformColumns is resulting in an error, at least the way I've set it up.

 

Can anyone assist?  @ImkeF the M Wizard?

 

I have a table as such:

Column Headers: Account | BilledMonth | Count | Spend Year 2 | Spend Year 1

 

1231/1/201712205.950
1232/1/20171858.17760
1233/1/20171837.52760
1234/1/20171235.12560
1235/1/20171947.13740
1236/1/201713698.36950
1237/1/201713857.93870
1238/1/201712439.86750
1239/1/201713319.23210
12310/1/201714480.25510
12311/1/201713846.06940
12312/1/20171-546.54120
1231/1/20181-4885.79640
1232/1/20181-5182.89060
7896/1/2016101054990.719
7897/1/2016101315482.823
7898/1/2016101323420.151
7899/1/2016101651866.191
78910/1/2016102181344.887
78911/1/2016102364539.433
78912/1/201622226594.4950
7891/1/201721911725.760
7892/1/201721319431.5170
7893/1/201721792099.6680
7894/1/201721705345.4230
7895/1/201722147638.8280
7896/1/20172-82614.03870
7897/1/2017258479.77070
7898/1/2017262815.71490
7899/1/2017282144.66310
78910/1/20172353836.20040
78911/1/20172679793.47730
78912/1/201721147928.5340
7891/1/201821428323.8330
7892/1/201821374781.0230
1 ACCEPTED SOLUTION

Solved:

 

Table.FromRecords(Table.TransformRows(#"Pivoted Column", (row) => 
        let
            Count = row[Count],
            OtherColumns = List.RemoveItems(Record.FieldNames(row), {"Account", "Billed Month"}),
            Transforms = List.Transform(OtherColumns, (name) => { name, (cell) => cell / Count})
        in
            Record.TransformFields(row, Transforms)))

View solution in original post

1 REPLY 1

Solved:

 

Table.FromRecords(Table.TransformRows(#"Pivoted Column", (row) => 
        let
            Count = row[Count],
            OtherColumns = List.RemoveItems(Record.FieldNames(row), {"Account", "Billed Month"}),
            Transforms = List.Transform(OtherColumns, (name) => { name, (cell) => cell / Count})
        in
            Record.TransformFields(row, Transforms)))

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.