Forum Discussion
Delete rows for some columns to align the data
I have a table, extracted from Sage50 to Excel.
The data is unfortunately not alligned correctly. I need to delete the first row for the last 5 columns. Is there a way to do this?
Thanks.
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.
8 Replies
Read about Fill Up/Fill Down.
Sorry, i don't get how if I fill up or down it will align my columns. If I fill up the last 5 columns, I will only get duplicates of everything. If I fill down the first 2 columns, I will get a whole bunch of unwanted rows.
I need to delete the first rows of the last 5 columns so that after I can delete all the empty rows and then I will fill down to ensure that all the info is there.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Sorry 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 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.
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 $
