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]

right click on that column and : Move - To Beginning

 

[Joyner]

Thanks, I did that but the line of code still lists all of the other columns, some that will be deleted in the futures and others are added (they are date column heading that shift periodically, old dated drop off, new ones are added).

 

[sandy666]

it doesn't make sense to me
create new table with dates and create relationship between them
then you can add/remove columns without any error(s) (probably)

Have a nice day

 

[Joyner]

I think that is what I did, I have a table of the dates, but it is a bit more complicated perhaps because users enter data into one table used for a query so I most maintain the position for when new dates are added and people are also added and drop off.

I actually have it working as planned and tested, now just this last bit about the reorder of one column.


Can I ask one more question, when I do a merge query, can I somehow specify that it add the query field to the beginning of the table and not the end as it is doing? If I can, then my problem is solved.

Or I add a numbered row to my query an sort the column that way maybe.

Thank you very much again for what you have provided. It has gotten me almost to the finish line.

 

[sandy666]

I think that is what I did

I really don't know what you did

can I somehow specify that it add the query field to the beginning of the table and not the end as it is doing.

No, you need one more step:

select each column (with Ctrl key) in order you want then right click and Remove Other Columns and it will reorder your table as you wish but if you forget about one or two columns they will be removed

and no one more question

 

[billszysz]

Maybe will be better without transposing and hardcoded column name?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source, Table.SelectRows(Record.ToTable(Table.Last(Source)), each [Value] <> null)[Name])
in
    #"Removed Other Columns"

 

[Joyner]

Bill Thank you, that seems to work perfect without the transpose - a good one to know. I still have the one issue of reordering columns and the refresh with the old column names. I have been searching with no luck, do you know of any code that can move a specific column without have to list all other column names?

Using "MissingField.Ignore", removed the error of the columns previously removed, but when I add new columns with another query, the new columns are properly added to the end, but for some reason, the refresh and Reorder step moves the new columns to the left one position with each refresh.


I hope that is clear, thanks

 

[Joyner]

Or maybe another solution, can I sort my columns by the values in the last row that go across the columns?

Thanks

 

[billszysz]

I don't know if i understand correctly your needs but.....check this code below

let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source, Table.SelectRows(Record.ToTable(Table.Last(Source)), each [Value] <> null)[Name]),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "New", each "b"),
    SortingByLastRow = Table.ReorderColumns(#"Added Custom", Table.Sort(Record.ToTable(Table.Last(#"Added Custom")), {{"Value", 0}})[Name])
in
    SortingByLastRow

 

[Joyner]

Bill, thank you so much, the sorting by last row line of code did. Everything appears to be working great.