Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JaweedL
Helper I
Helper I

Concatenate Following Line with Previous One in PowerBi

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.

 

DateDescriptionDebitCredit 
12-Oct-24Salary  10000 
16-Oct-24Food200  
18-Oct-24Restaurant500  
20-Oct-24Loan Repayment to1000  
 ABC Bank   
21-Oct-24Food200  
21-Oct-24Gift obtained 500 
22-Oct-24Motor Vehicles   
 Repairs200  
24-Oct-24Birthday Gifts 600 
 from families   
1 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.

 

View solution in original post

11 REPLIES 11
danextian
Super User
Super User

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

danextian_0-1728989886578.png

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.

danextian_1-1728989951936.png

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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
Community Champion
Community Champion

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.


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

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.

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"


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

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}}),

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"

 

SamWiseOwl_0-1729067040688.png

 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

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
Resolver III
Resolver III

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

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.

 

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


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.