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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kar2022
Frequent Visitor

Adding calculated Rows using Power Query

Hi,

I am struggling to add rows in the same table using Power Query.  Basically, I want to used the row data (by date) and create additional rows (Cal1 and Cal2) for the same dates and calculating as below.

 

DatePlayerScore
6/1/2022Player1-0.1197
6/1/2022Player2-0.1250
6/1/2022Player3-0.1159
5/1/2022Player1-0.0159
5/1/2022Player2-0.0166
5/1/2022Player3-0.0066
4/1/2022Player1-0.1349
4/1/2022Player2-0.1169
4/1/2022Player3-0.1098
6/1/2022Cal1(Player1, 6/1 score)*0.25 + (Player2, 6/1 score)*0.4 + (Player3, 6/1 score)*0.35]
6/1/2022Cal2(Player1, 6/1 score)*0.56  + (Player3, 6/1 score)*0.44]
5/1/2022Cal1(Player1, 5/1 score)*0.25 + (Player2, 5/1 score)*0.4 + (Player3, 5/1 score)*0.35]
5/1/2022Cal2(Player1, 5/1 score)*0.56  + (Player3, 5/1 score)*0.44]
4/1/2022Cal1(Player1, 4/1 score)*0.25 + (Player2, 4/1 score)*0.4 + (Player3, 4/1 score)*0.35]
4/1/2022Cal2(Player1, 4/1 score)*0.56  + (Player3, 4/1 score)*0.44]

 

Many Thanks.

VC

 

 

1 ACCEPTED SOLUTION

Hi Greg,

 

Thank you very much.  It works fine.  However, when I make changed in my file it does not works.  I "Appended" a new data file with "Date", "Player", "Score" for a new player.

 

Where would I add it?.  Not very good with "M".  Thanks for the helpl

 

    Custom = Table.Combine(#"Added Custom"[Custom],{"Player”,"Date", "Score","PrevScore"}),

    #"Added Custom1" = Table.AddColumn(Custom, "Player", each ([Score]-[PrevScore])/[PrevScore]),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Score", "PrevScore"}),

    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", Player", "Score"}),

    #"Appended Query" = Table.Combine({#"Reordered Columns", Captain})

in

    #"Appended Query"

View solution in original post

8 REPLIES 8
AlexisOlson
Super User
Super User

Here's a method that should generalize well if you have lots more players and calculations:

let
    Weights = #table(
        type table [Player = Text.Type, Calc1 = Number.Type, Calc2 = Number.Type],
        {{"Player1", 0.25 , 0.56},{"Player2", 0.4, null},{"Player3", 0.35, 0.44}}
    ),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc8xDoAwCIXhuzCrBdqi3MK96eDg5uTm7TWmbLAxfOHlbw0kUWJkhgn263jOm75rxoVIV+iTA3gAruiDbB+q/qAGExgCNiDigzGBOECJKnJRH1gFSQCsAnWD3l8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Player = _t, Score = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Player", type text}, {"Score", type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Player"}, Weights, {"Player"}, "Weights", JoinKind.LeftOuter),
    #"Expanded Weights" = Table.ExpandTableColumn(#"Merged Queries", "Weights", {"Calc1", "Calc2"}, {"Calc1", "Calc2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Weights",{"Player"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Date", "Score"}, "Player", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Product", each [Score] * [Value], type number),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Date", "Player"}, {{"Score", each List.Sum([Product]), type number}}),
    #"Appended Query" = Table.Combine({#"Changed Type", #"Grouped Rows"})
in
    #"Appended Query"

This merges in the weights corresponding to each player and calculation, unpivots the calculations, takes the product of the score and weight value, uses Group By to sum the products, and appends these calculated rows to the end of the starting table.

 

In practice, your Weights would be specified in a different table like the following but I've included the definition in the query above so that it's entirely self-contained and you can just paste it as-is into the Advanced Editor of a new blank query.

AlexisOlson_0-1662673008702.png

v-stephen-msft
Community Support
Community Support

Hi @kar2022 ,

 

You could try to pivot columns and then get the calculated results.

Pivot columns - Power Query | Microsoft Docs

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Stephen,

Thanks.  Should I unpivot once the calculation is completed?

@kar2022 Correct, take a look at this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc8xDoAwCIXhuzCrBdqi3MK96eDg5uTm7TWmbLAxfOHlbw0kUWJkhgn263jOm75rxoVIV+iTA3gAruiDbB+q/qAGExgCNiDigzGBOECJKnJRH1gFSQCsAnWD3l8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Player = _t, Score = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Player", type text}, {"Score", type number}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Player]), "Player", "Score", List.Sum),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Cal1", each [Player1] * .25 + [Player2] * .4 + [Player3] * .35),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Player1] * .56 + [Player3] * .44),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom1", {"Date"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Just paste into a Blank query using the Advanced Editor.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,

Thank you very much.  I solved the problem.  My only question is, I used "reference" to create a new sheet and added the formulas.  Is this the correct way to do it?. 

Hi Greg,

 

Thank you very much.  It works fine.  However, when I make changed in my file it does not works.  I "Appended" a new data file with "Date", "Player", "Score" for a new player.

 

Where would I add it?.  Not very good with "M".  Thanks for the helpl

 

    Custom = Table.Combine(#"Added Custom"[Custom],{"Player”,"Date", "Score","PrevScore"}),

    #"Added Custom1" = Table.AddColumn(Custom, "Player", each ([Score]-[PrevScore])/[PrevScore]),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Score", "PrevScore"}),

    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", Player", "Score"}),

    #"Appended Query" = Table.Combine({#"Reordered Columns", Captain})

in

    #"Appended Query"

If I add prior to the Append Query, what sytex should I use?

Hi Greg,

I was to reference my file (prior to Append) and manage to do it.  Is this a good practice?.  Also, how do I add another custom formula?

(#"Added Custom1"),  In our example, I want to add a new "Cal3" (like we have Cal1 and Cal2).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors