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!