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 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"
Helper IThanks 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.