Delete rows for some columns to align the data

[JoelDucharme]

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.

[JoelDucharme]

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

[lbendlin · Accepted solution]

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.

[JoelDucharme]

I think it worked! Thank you very much! I really have to take the time to learn Dax better.

[JoelDucharme]

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.

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

Read about Fill Up/Fill Down.