M Code - remove all columns where the last row is null for that column

[Joyner]

Hello everyone and thank you for the help.

Is this possible?

I am a doing an append query and if the last row has a null value, I want to remove that particular column that has the null value in the last row, there could be multiple columns with null.

Thank you.

 

[sandy666]

maybe:
- Transpose
- Go to the last column
- filter by null
- Transpose back

 

[Joyner]

Wow, that works, thank you

One problem, maybe I did something wrong, but it renamed all of my column heading to Column 1, Column 2, etc..

Any advice to correct that, thanks.

 

[sandy666]

here is an example:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column3[/td][td=bgcolor:#5B9BD5]Column4[/td][td][/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Column4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]a[/td][td=bgcolor:#DDEBF7]d[/td][td=bgcolor:#DDEBF7]i[/td][td=bgcolor:#DDEBF7]m[/td][td][/td][td=bgcolor:#E2EFDA]d[/td][td=bgcolor:#E2EFDA]m[/td][/tr]

[tr=bgcolor:#FFFFFF][td]b[/td][td]e[/td][td]j[/td][td]n[/td][td][/td][td]e[/td][td]n[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]c[/td][td=bgcolor:#DDEBF7]f[/td][td=bgcolor:#DDEBF7]k[/td][td=bgcolor:#DDEBF7]o[/td][td][/td][td=bgcolor:#E2EFDA]f[/td][td=bgcolor:#E2EFDA]o[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]g[/td][td]l[/td][td]p[/td][td][/td][td]g[/td][td]p[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]h[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]r[/td][td][/td][td=bgcolor:#E2EFDA]h[/td][td=bgcolor:#E2EFDA]r[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each ([Column6] <> null)),
    #"Transposed Table1" = Table.Transpose(#"Filtered Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"[/SIZE]

as you can see my original headers are Column1, Column2 and so on...
Demote and Promote is to save your original headers

 

[Joyner]

Thank you so much and thank you for the speedy reply. I got it working that is brilliant.

So another question if I can. So after I apply the procedure as you outline, as I want, some columns are deleted and others are then added. Now I have a problem that when I refresh, I get an error that the column names I deleted are not found - they are in my code in a previous step when I reorder columns. Since that code lists the order of all columns by specific reference, including now the ones deleted, if error on the line where the deleted columns are referenced.

Do you happen to have any tricks for that issue (or course I am just using the code as written when I click through the steps, so I am probably using a non-dynamic code).

Thanks again

 

[sandy666]

it was the easiest way to achieve what you wanted in the first post.
manipulating the structure of the data source is a very bad idea, so it's best to plan what the source table should look like so that you don't improve it later.
by changing the source data structure you prevent the correct operation of the previously defined M-code (in most cases)
In this case you change the structure of the source and at the same time you change the structure of Query-table (Transpose, Remove Columns)
the thing you want to achieve now is much more complicated and requires time and all necessary information about the structure, what changes you will do and some more.
it's best to provide a link to a shared excel file with an example of the data source and based on what you want to achieve

 

[Joyner]

Hello Sandy, so I found "MissingField.Ignore". It seems to works, is this a safe and correct workaround?

Thanks

 

[Joyner]

My line for reordering columns is doing some strange things, with each refresh the two columns that are new, and therefore not specifically referenced in my reorder line of code, start on the end where they belong, but keep moving one position to the left with each refresh.

So I think I need to rewrite the line of code.

What I need to do in that step, is reorder the last column ( a created conditional column) to the front. Can I write it like "move column xyz to position one, and avoid listing all of the other columns in the code as it is automatically written?

Hope that is clear - Thanks

 

[sandy666]

MissingField.Ignore, is this a safe and correct workaround?

this is optional parameter in record and table

whole PQ is safe and works well if you know what are you doing

edit:

re-read last line in post#6

 

[Joyner]

Thanks, I'll take a look at the post.

So one bit of correction is the column I have to move from the end is put there as the result of a merge query, but I need the column to be the first column position for my output. So I need he last column moved to the first without specific reference to the remaining columns.

I am new to this so maybe my overall understanding of the structure / planning is flawed.

Thanks again