Concatenate Following Line with Previous One in PowerBi

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!

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.

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.