Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Combine variable number of row values into a single row

I am using Power Query and I have the data in table 1 below where the description in some cases is on two or more rows and in some cases it is a in a single row.  I need to concatenate the description values into a single cell as in table 2 below.  Thank  you in advance for any help or guidance.

 

Table 1

GL DateEff DateDescriptionDebitCreditBalanceReference
01 Feb 202401 Feb 20248401683590478 FIXED 775.00USD 1,258,816.97100IC1UUSD000001
01 Feb 202401 Feb 2024DEPOSIT INTEREST  USD 1,258,816.97100IC1UUSD000001
30 Jan 202431 Jan 2024CREDIT INTEREST 7.99USD 1,258,041.97100IC19USD000001
30 Jan 202431 Jan 2024GOV STAMP DUTY1.20 USD 1,258,033.98100I1GSUSD000001
30 Jan 202430 Jan 2024WT FROM - 6066880 - BC 95,347.00USD 1,258,035.182403003326416000
30 Jan 202430 Jan 2024FUND ABC   USD 1,258,035.182403003326416000
30 Jan 202430 Jan 2024CLEARING -- /BNF/REF JOE  USD 1,258,035.182403003326416000
30 Jan 202430 Jan 2024CAPITAL FUND XYZ  USD 1,258,035.182403003326416000
30 Jan 202430 Jan 2024RRN0000000000234648  USD 1,258,035.182403003326416000

 

Table 2

GL DateEff DateDescriptionDebitCreditBalanceReference
01 Feb 202401 Feb 20248401683590478 FIXED DEPOSIT INTEREST 775.00USD 1,258,816.97100IC1UUSD000001
30 Jan 202431 Jan 2024CREDIT INTEREST 7.99USD 1,258,041.97100IC19USD000001
30 Jan 202431 Jan 2024GOV STAMP DUTY1.20 USD 1,258,033.98100I1GSUSD000001
30 Jan 202430 Jan 2024WT FROM - 6066880 - BC FUND ABC CAPITAL FUND XYZ RRN0000000000234648 95,347.00USD 1,258,035.182403003326416000

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    I was able to piece together a not so elegant solution.  Here is the code from the advanced editor

     

        #"Added Custom1" = Table.AddColumn(#"Filled Down", "LE Amount", each if [Debit Amount.2] <> null then "Y " & [Debit Amount.2] else "N " & [Credit Amount.2]),
    #"Filled Down1" = Table.FillDown(#"Added Custom1",{"LE Amount"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Filled Down1",{"Debit Amount.2", "Credit Amount.2"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Page001", "Transaction Date", "Value Date", "Balance", "Reference Number", "Legal Entity", "CCY", "LE Amount"}, {{"Transaction Comments", each Text.Combine([Description], " "), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "LE Amount", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"LE Amount.1", "LE Amount.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"LE Amount.1", "Is Debit"}, {"LE Amount.2", "LE Amount"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Y","YES",Replacer.ReplaceText,{"Is Debit"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","N","NO",Replacer.ReplaceText,{"Is Debit"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"LE Amount", Currency.Type}})
in
    #"Changed Type"

     Basically my problem was the Debit and Credit columns which were preventing me from grouping properly.  I added a custom column with a prefix, Y for Debit and N for Credit and then deleted the Debit and Credit columns.  Then I filled down the values in the new column and removed the original Debit and Credit columns.  Then using a trick I picked up from this forum about grouping and combining values, I created the GroupBy and adjusted the code to use Text.Combine.  That successfully combined my description values and removed the unwanted rows.  The code after the GroupBy clause is just some clean up to get back to Debits and Credits with a Single Amount column.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I was able to piece together a not so elegant solution.  Here is the code from the advanced editor

     

        #"Added Custom1" = Table.AddColumn(#"Filled Down", "LE Amount", each if [Debit Amount.2] <> null then "Y " & [Debit Amount.2] else "N " & [Credit Amount.2]),
    #"Filled Down1" = Table.FillDown(#"Added Custom1",{"LE Amount"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Filled Down1",{"Debit Amount.2", "Credit Amount.2"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Page001", "Transaction Date", "Value Date", "Balance", "Reference Number", "Legal Entity", "CCY", "LE Amount"}, {{"Transaction Comments", each Text.Combine([Description], " "), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "LE Amount", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"LE Amount.1", "LE Amount.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"LE Amount.1", "Is Debit"}, {"LE Amount.2", "LE Amount"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Y","YES",Replacer.ReplaceText,{"Is Debit"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","N","NO",Replacer.ReplaceText,{"Is Debit"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"LE Amount", Currency.Type}})
in
    #"Changed Type"

     Basically my problem was the Debit and Credit columns which were preventing me from grouping properly.  I added a custom column with a prefix, Y for Debit and N for Credit and then deleted the Debit and Credit columns.  Then I filled down the values in the new column and removed the original Debit and Credit columns.  Then using a trick I picked up from this forum about grouping and combining values, I created the GroupBy and adjusted the code to use Text.Combine.  That successfully combined my description values and removed the unwanted rows.  The code after the GroupBy clause is just some clean up to get back to Debits and Credits with a Single Amount column.