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
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 |
Solved! Go to 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.
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.
Proud to be a Super User!
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.
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"
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.
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:
Load the Data into Power Query:
Add an Index Column:
Identify and Fill Down the Dates:
Identify Concatenated Rows:
Fill Down the Descriptions:
Merge the Descriptions:
Filter Out SubLines:
Filter the rows where IsSubLine is "Yes" and remove them since their content has been concatenated with the previous row.
Remove the Helper Columns:
Remove the IsSubLine column and the Index column.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
103 | |
98 | |
90 | |
71 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |