Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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:
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
Proud to give back to the community!
Thank You!
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |