Forum Discussion
Helper IConcatenate Following Line with Previous One in PowerBi
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.
Super UserHi 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.
Helper IThank 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"