Transforming data

[M_Tugz]

Good day all,

I'm pretty new to Power BI, never coded in my life and desperately need your expertise and help. I would like to clean up and create a single row for my data. However, as you will see in the extract below, my reports has has inconsistent outputs where sometimes a transactions has 3 rows showing an account and an additional reference and sometimes two rows with just one refeence. I'm not sure if there's a single transformation in PowerBI i can use (I'm not yet on DAX!!)

Post date	Reference	Narrative	Value Date		Debit	Credit	Closing Balance
21-Jul-17	FT172P6MNK	Payment	21-Jul-17			1.02	1.02
		TR14322
		AC - 45221
21-Jul-17	FT172S00XW	Agent Transfer	21-Jul-17			1.02	2.04
		TR54322

[fhill]

There is a 'Fill Down' option in Query Editor to fill blank spaces with values from previous rows.  This could be useful to assign data to blank rows, but be careful with Fill when it comes to the Credits / Debits you have listed not to duplicate / triplicate this data erronoursly.  

Consider something along these lines.... 1. Duplicate your Table and only keep the neeeded columns associated with Credit / Debit columns and remove all the other rows with blanks.  (You'll probably want to un-pivot those two also to bring them together eventually....?)

2. Once youv'e stripped out datat that would become false when Filled Donw, then Fill Down with the remaining columns.  

3. Start buliding relationshpis to link your data back together.

If you post a few more rows of data, we can better show this process in examples.  FOrrest

[MarcelBeug · Accepted solution]

At the same time I was preparing a similar solution along with a video for clarification.

I made some assumptions (about table keys and how the result should look like).

The code:

let
    Source = Table1,
    #"Filled Down" = Table.FillDown(Source,{"Post date", "Reference"}),
    Narratives = Table.Group(#"Filled Down", {"Post date", "Reference"}, {{"Narrative", each Text.Combine([Narrative], "#(lf)"), type text}}),
    Custom1 = Source,
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"Narrative"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Merged Queries" = Table.NestedJoin(#"Removed Blank Rows",{"Post date", "Reference"},Narratives,{"Post date", "Reference"},"Narrative",JoinKind.LeftOuter),
    #"Expanded Narrative" = Table.ExpandTableColumn(#"Merged Queries", "Narrative", {"Narrative"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Narrative",{"Post date", "Reference", "Narrative", "Value Date", "Debit", "Credit", "Closing Balance"})
in
    #"Reordered Columns"

The video: