Forum Discussion
Adding calculated Rows using Power Query
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"
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.
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).