Forum Discussion
Helper IDelete rows for some columns to align the data
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc9BCsMgFEXRvTh28t9bQdroJiQ7CJ11/613GD4BM7mCPNAzRomt1BKvfz7f88yPo47LdbxjdqeN9kh3Ykcb7Up3Zkcb7U5293/UtGjVIizCIixKLcIiLMKi1CIswiIsemDxtHjVYizGYixOLcZiLMbi1HLz9vED", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t, #"Column D" = _t, #"Column E" = _t, #"Column F" = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"Column A","Column B","Column C","Column D","Column E","Column F"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column A", "Column B"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column C] <> null)) in #"Filtered Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
Helper ISorry about that. Still learning. I hope the following will be better to help.
So here is an example of what I have.
| Column A | Column B | Column C | Column D | Column E | Column F |
| 1A | 1B | null | null | null | null |
| null | null | 1C1 | 1D1 | 1E1 | 1F1 |
| null | null | 1C2 | 1D2 | 1E2 | 1F2 |
| null | null | 1C3 | 1D3 | 1E3 | 1F3 |
| null | null | null | null | null | null |
| 2A | 2B | null | null | null | null |
| null | null | 2C1 | 2D1 | 2E1 | 2F1 |
| null | null | 2C2 | 2D2 | 2E2 | 2F2 |
| null | null | 2C3 | 2D3 | 2E3 | 2F3 |
| null | null | null | null | null | null |
| 3A | 3B | null | null | null | null |
| null | null | 3C1 | 3D1 | 3E1 | 3F1 |
| null | null | 3C2 | 3D2 | 3E2 | 3F2 |
| null | null | null | null | null | null |
By deleting the first row of Columns C to F, everything will be aligned.
This is what I need the data to look like in the end, after realigning things, deleting empty rows and fill down for Columns A and B.
| Column A | Column B | Column C | Column D | Column E | Column F |
| 1A | 1B | 1C1 | 1D1 | 1E1 | 1F1 |
| 1A | 1B | 1C2 | 1D2 | 1E2 | 1F2 |
| 1A | 1B | 1C3 | 1D3 | 1E3 | 1F3 |
| 2A | 2B | 2C1 | 2D1 | 2E1 | 2F1 |
| 2A | 2B | 2C2 | 2D2 | 2E2 | 2F2 |
| 2A | 2B | 2C3 | 2D3 | 2E3 | 2F3 |
| 3A | 3B | 3C1 | 3D1 | 3E1 | 3F1 |
| 3A | 3B | 3C2 | 3D2 | 3E2 | 3F2 |
- lbendlin
Super User
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc9BCsMgFEXRvTh28t9bQdroJiQ7CJ11/613GD4BM7mCPNAzRomt1BKvfz7f88yPo47LdbxjdqeN9kh3Ykcb7Up3Zkcb7U5293/UtGjVIizCIixKLcIiLMKi1CIswiIsemDxtHjVYizGYixOLcZiLMbi1HLz9vED", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t, #"Column D" = _t, #"Column E" = _t, #"Column F" = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"Column A","Column B","Column C","Column D","Column E","Column F"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column A", "Column B"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column C] <> null)) in #"Filtered Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
- JoelDucharme
Thanks.
I'm sorry but I didn't think about the column types with the sample data.
I noticed in the code that there is a column type as text. Would this change something? I tried it with the sample data and it works, but not with the different column types.
This is more how it would look.
Account Number (number) Account Description (text) Date (Date) Description (Text) N°ÉJ (Text) Montant (Dollar) 5574 1B null null null null null null 2024-01-02 Description 1 1E1 10,00 $ null null 2024-01-06 Description 2 1E2 263,00 $ null null 2024-09-08 Description 3 1E3 235,67 $ null null null null null null 5578 2B null null null null null null 2024-10-03 Description 4 2E1 345,68 $ null null 2024-11-07 Description 5 2E2 234.65 null null null null null null 5698 3B null null null null null null 2024-02-05 Description 6 3E1 34 554,55 $ null null 2024-03-07 Description 7 3E2 453,00 $ null null null null null null 5679 4B null null null null null null 2024-01-07 Description 8 4E1 6 624,40 $ null null 2024-02-26 Description 9 4E2 8 456,30 $ null null 2024-11-15 Description 10 4E3 56 798,00 $ - lbendlin
Not really - the process is the same. Fill down the first two columns. Then filter out the nulls in the third column.