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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
dkay84_PowerBI
Microsoft Employee
Microsoft Employee

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
dkay84_PowerBI
Microsoft Employee
Microsoft Employee

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
dkay84_PowerBI
Microsoft Employee
Microsoft Employee

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
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.