Concatenate Following Line with Previous One in PowerBi

[JaweedL]

Hi all

I have extracted a Bank Statement  with PowerAutomate and got it inserted in Excel, which will be read by PowerBi.

 At times, the line in the Bank statement is split over 2 lines. I attach a sample from an Excel Sheet for reference. How can I get , on the 20th Oct , the Description to read  "Loan Repayment to ABC Bank",  I mean the following line gets concatenated with the first one. 

Similarly for 22 and 24 Oct.  

It does not matter where the the transformation is done ,be it at Excel Level ( Source) or in PowerBi ( Destination). The final goal is to have a proper Description in Powerbi.

I highly appreciate some help.

Date	Description	Debit	Credit
12-Oct-24	Salary		10000
16-Oct-24	Food	200
18-Oct-24	Restaurant	500
20-Oct-24	Loan Repayment to	1000
	ABC Bank
21-Oct-24	Food	200
21-Oct-24	Gift obtained		500
22-Oct-24	Motor Vehicles
	Repairs	200
24-Oct-24	Birthday Gifts		600
	from families

[danextian]

Hi @JaweedL 

there are two options in the attached pbix. The first one fills down the date in power query and uses concatenatex to combine row values

the second one uses a index column to pick the row value that is either of a higher or lower value than the current index.

they will work given the sample data but if there are more than two blank consecutive rows, the formula will return an incorrect result for the second option.

[JaweedL · Accepted solution]

Hi Sir

The Second solution works, whereas the first one needs a tweek, It concatenates two valid transactiions if on the same date when it should not. Refer to 21 Oct ( Still in Solution 1).

Solution 2 seems to work perfectly. Kudos, kudos.

[SamWiseOwl]

Hi @JaweedL 

If you copy this into the Advanced Editor it will recreate my logic.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDBbsIwDEB/xcq5SDQCtCsFwWUTEpN2qTiY1hUWbVyl5tC/X1uVpmOTlovj+MUvTpqaPSqZyOypyTzXyuKG7MraxZ2nfNiAuUSpie3ilOnCrrqTTyzRtzDUIhMvuxW4TeAOInkX7FiekLeAnKlRfHh0vWn9AtplAN8FHZypxrYip6Aymn9c6HfbZAcJuvtUmLrF/75sjhy5UJCrIjvKR2o9G9TOPuRDVDx80Y2zkppfZhgGrZF985d0FRol7PWWYwu9/dloM7P2eeGlggIrLvlVdvkG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Description", type text}, {"Debit", Int64.Type}, {"Credit", Int64.Type}, {" ", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{" "}),
#"Filled Down" = Table.FillDown(#"Removed Columns",{"Date", "Debit", "Credit"}),
#"Added Custom" = Table.AddColumn(#"Filled Down", "UniqueCode", each Text.From([Date])&Text.From([Debit])&Text.From([Credit])),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"UniqueCode"}, #"Added Custom", {"UniqueCode"}, "Added Custom", JoinKind.LeftOuter),
#"Aggregated Added Custom" = Table.AggregateTableColumn(#"Merged Queries", "Added Custom", {{"Description", Text.Combine , "List of Description"}}),
#"Removed Duplicates" = Table.Distinct(#"Aggregated Added Custom", {"List of Description"}),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Duplicates",{"Description", "UniqueCode"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Date", "List of Description", "Debit", "Credit"})
in
#"Reordered Columns"

Create a unique code by combining the date, debit and credit.

Join the table to itself using this code.

Expand the rows but use Text.Combine to join the same rows together.

Remove the duplicate rows.

Remove the extra columns.

[JaweedL]

Thank you Sir.

The description get merged. But my problem, I cannot refresh.  The data set does not get refreshed. I anm using an Excel Sheet on my desktop. Any guidance for me? Thanks.

[SamWiseOwl]

Hi @JaweedL 

Change the location and sheet name to yours 🙂

let
Source = Excel.Workbook(File.Contents("C:\Users\SGLow\Desktop\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Description", type text}, {"Debit", Int64.Type}, {"Credit", Int64.Type}, {" ", type text}}),

#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{" "}),
#"Filled Down" = Table.FillDown(#"Removed Columns",{"Date", "Debit", "Credit"}),
#"Added Custom" = Table.AddColumn(#"Filled Down", "UniqueCode", each Text.From([Date])&Text.From([Debit])&Text.From([Credit])),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"UniqueCode"}, #"Added Custom", {"UniqueCode"}, "Added Custom", JoinKind.LeftOuter),
#"Aggregated Added Custom" = Table.AggregateTableColumn(#"Merged Queries", "Added Custom", {{"Description", Text.Combine , "List of Description"}}),
#"Removed Duplicates" = Table.Distinct(#"Aggregated Added Custom", {"List of Description"}),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Duplicates",{"Description", "UniqueCode"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Date", "List of Description", "Debit", "Credit"})
in
#"Reordered Columns"

[JaweedL]

Thanks a lot Sir.

It seems, under your correction, not to work. It does not recognise {" ", type text}, which I removed, and amened the line after. The output is not correct.

#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Description", type text}, {"Debit", Int64.Type}, {"Credit", Int64.Type}, {" ", type text}}),

[SamWiseOwl]

Hi @JaweedL  thanks for replying

I think this is the difference between your example data and the excel sheet.

It thinks the columns don't match.

There is an extra blank column in mine perhaps.

Try this, I've remove reference to this column.

let
Source = Excel.Workbook(File.Contents("C:\Users\SGLow\Desktop\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Description", type text}, {"Debit", Int64.Type}, {"Credit", Int64.Type}}),

#"Filled Down" = Table.FillDown(#"Changed Type1",{"Date", "Debit", "Credit"}),
#"Added Custom" = Table.AddColumn(#"Filled Down", "UniqueCode", each Text.From([Date])&Text.From([Debit])&Text.From([Credit])),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"UniqueCode"}, #"Added Custom", {"UniqueCode"}, "Added Custom", JoinKind.LeftOuter),
#"Aggregated Added Custom" = Table.AggregateTableColumn(#"Merged Queries", "Added Custom", {{"Description", Text.Combine , "List of Description"}}),
#"Removed Duplicates" = Table.Distinct(#"Aggregated Added Custom", {"List of Description"}),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Duplicates",{"Description", "UniqueCode"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Date", "List of Description", "Debit", "Credit"})
in
#"Reordered Columns"

[JaweedL]

Thanks Sam for looking into.

Just at looking at the ouput, unfortunately the report is yielding wrong output. We are having both Debit and Credit filled. wheras in the sample data provided, it is either one or another. 

[grazitti_sapna]

Hi @JaweedL ,

To concatenate the split lines in Power BI and achieve a proper description for each transaction, you can use Power Query to achieve this. Follow the steps below:

1. Load the Data into Power Query:

   • In Power BI, go to the Home tab and select Transform Data to open Power Query Editor.
   • Load your bank statement data.

2. Add an Index Column:

   • Go to the Add Column tab.
   • Click Index Column > From 1. This will help identify the rows that should be combined.

3. Identify and Fill Down the Dates:

   • In the Date column, select the cells, go to the Transform tab, and choose Fill Down. This will fill down the dates for the rows where only the description is present.

4. Identify Concatenated Rows:

   • Add a Conditional Column to identify if the rows contain a description split across two lines:
     • Go to the Add Column tab and select Conditional Column.
     • Name it IsSubLine and create a rule: If the Description column equals null, then set it as "Yes". Otherwise, set it as "No".

5. Fill Down the Descriptions:

   • In the Description column, go to the Transform tab and select Fill Down. This will fill down the descriptions where they are split across multiple rows.

6. Merge the Descriptions:

   • Create a Custom Column to concatenate the descriptions:
     • Go to the Add Column tab and select Custom Column.
     • Use the formula:                                                                                                                                   if [IsSubLine] = "Yes" then null else Text.Combine(List.RemoveNulls({[Description], try #"Previous Row".[Description] otherwise null}), " ")
     • This will merge the main description with its subsequent part.

7. Filter Out SubLines:

   • Filter the rows where IsSubLine is "Yes" and remove them since their content has been concatenated with the previous row.

8. Remove the Helper Columns:

   • Remove the IsSubLine column and the Index column.

9. Close & Apply:

   • Click Close & Apply to load the transformed data back into Power BI.

This process should give you a properly formatted description in Power BI.

If I have resolved your question, please consider marking my post as a solution. Thank you!

[JaweedL]

Hi

Thank you very much. I am still learning PowerBi.

In Step 6 above, 

[IsSubLine] = "Yes" then null else... It throws me an error on the null in red.

In Step 5, I did not see any "Yes" appearing.

can you guide me, please. The solution proposed seems a nice one. Many thanks.

[SamWiseOwl]

Very smooth, I like the idea of testing if main or subline!