Forum Discussion

kar2022's avatar
kar2022
Frequent Visitor
3 years ago
Solved

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 a...
Need Help
  • kar2022's avatar
    kar2022
    3 years ago

    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"

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.

kar2022's avatar
kar2022
Frequent Visitor
3 years ago

Hi Stephen,

Thanks.  Should I unpivot once the calculation is completed?

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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.

    • kar2022's avatar
      kar2022
      Frequent Visitor
      3 years ago

      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's avatar
        kar2022
        Frequent Visitor
        3 years ago

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

    • kar2022's avatar
      kar2022
      Frequent Visitor
      3 years ago

      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?.