Forum Discussion

Swalker's avatar
Swalker
Frequent Visitor
3 years ago
Solved

PowerQuery M Running Total Date and LedgerKey

Hi All,

 

I am trying to convert Transactionlines (TL) into periodic snapshot for a balance sheet model using PowerQuery.

The TL table is loaded in PQ and has Date, AccountCode and Amount:

 

Next step is to create a new Column RTTable with EndOfMonth Dates - and for each AccountCode i like a Running Total. 

See picture for Accountcode 1600. Next to 1600 should be another Account Code column etc.

I am able to code this in DAX (see code below for eg AccountCode 1600) but howis this done in PowerQuery ?

Once realised i will unpivot the RTTable and it is then input for the balance sheet model.

 

Desired tabe:

DAX code:

1600 =var CurrentDate=RTTable[Date]
var CurrentLedger=1600
var FilteredTable = FILTER('TranactionLines','TransactionLines'[AltDate]<=CurrentDate && 'TransactionLines'[AccountKey]=CurrentLedger)
return
	CALCULATE(SUM('TransactionLines'[Amount]),FilteredTable)

 

 

any help is appreciated

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Swalker ,

     

    Please refer to the following steps:

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "ddBRDoQwCATQu/TbHWFaWjyL8f7XsJqazZb1i4+XYQj7nnTVlaKWlmR2D6WALR3LD2oV6eOjxWA5qPFSGlgfkxAlSsBeyrv0a/NWLRAP2IO5j9ogOSZHpStUH+W8lyAj6rhWvGGL4dFrjj/ZUdsE5VWnxTm86c3H1e7wqFLKpb5t6Hic", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [AltDate = _t, GLAccountCode = _t, AmountDC = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"AltDate", type date}, {"GLAccountCode", Int64.Type}, {"AmountDC", type number}}
  ), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"GLAccountCode"}, 
    {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}
  ), 
  Custom1 = Table.TransformColumns(
    #"Grouped Rows", 
    {
      "Data", 
      (x) => Table.AddColumn(x, "Running Total", each List.Sum(List.FirstN(x[AmountDC], [Index])))
    }
  ), 
  #"Expanded Data" = Table.ExpandTableColumn(
    Custom1, 
    "Data", 
    {"AltDate", "Running Total"}, 
    {"AltDate", "Running Total"}
  ), 
  #"Pivoted Column" = Table.Pivot(
    Table.TransformColumnTypes(#"Expanded Data", {{"GLAccountCode", type text}}, "en-US"), 
    List.Distinct(
      Table.TransformColumnTypes(#"Expanded Data", {{"GLAccountCode", type text}}, "en-US")[
        GLAccountCode
      ]
    ), 
    "GLAccountCode", 
    "Running Total", 
    List.Sum
  ), 
  #"Filled Down" = Table.FillDown(
    #"Pivoted Column", 
    {"5515", "1600", "1520", "5512", "5513", "1100", "440"}
  )
in
  #"Filled Down"

    result:

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Swalker ,

     

    Please refer to the following steps:

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "ddBRDoQwCATQu/TbHWFaWjyL8f7XsJqazZb1i4+XYQj7nnTVlaKWlmR2D6WALR3LD2oV6eOjxWA5qPFSGlgfkxAlSsBeyrv0a/NWLRAP2IO5j9ogOSZHpStUH+W8lyAj6rhWvGGL4dFrjj/ZUdsE5VWnxTm86c3H1e7wqFLKpb5t6Hic", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [AltDate = _t, GLAccountCode = _t, AmountDC = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"AltDate", type date}, {"GLAccountCode", Int64.Type}, {"AmountDC", type number}}
  ), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"GLAccountCode"}, 
    {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}
  ), 
  Custom1 = Table.TransformColumns(
    #"Grouped Rows", 
    {
      "Data", 
      (x) => Table.AddColumn(x, "Running Total", each List.Sum(List.FirstN(x[AmountDC], [Index])))
    }
  ), 
  #"Expanded Data" = Table.ExpandTableColumn(
    Custom1, 
    "Data", 
    {"AltDate", "Running Total"}, 
    {"AltDate", "Running Total"}
  ), 
  #"Pivoted Column" = Table.Pivot(
    Table.TransformColumnTypes(#"Expanded Data", {{"GLAccountCode", type text}}, "en-US"), 
    List.Distinct(
      Table.TransformColumnTypes(#"Expanded Data", {{"GLAccountCode", type text}}, "en-US")[
        GLAccountCode
      ]
    ), 
    "GLAccountCode", 
    "Running Total", 
    List.Sum
  ), 
  #"Filled Down" = Table.FillDown(
    #"Pivoted Column", 
    {"5515", "1600", "1520", "5512", "5513", "1100", "440"}
  )
in
  #"Filled Down"

    result:

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum