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



## Joyner (Aug 8, 2019)

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 (Aug 8, 2019)

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


----------



## Joyner (Aug 8, 2019)

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 (Aug 8, 2019)

here is an example:


*Column1**Column2**Column3**Column4**Column2**Column4*adimdmbejnencfkofoglpgphrhr


```
[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 (Aug 9, 2019)

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 (Aug 9, 2019)

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 (Aug 9, 2019)

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

Thanks


----------



## Joyner (Aug 9, 2019)

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 (Aug 9, 2019)

Joyner said:


> 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 (Aug 9, 2019)

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


----------



## Joyner (Aug 8, 2019)

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 (Aug 9, 2019)

right click on that column and : Move - To Beginning


----------



## Joyner (Aug 9, 2019)

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 (Aug 9, 2019)

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 (Aug 9, 2019)

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 (Aug 9, 2019)

Joyner said:


> I think that is what I did



I really don't know what you did 



Joyner said:


> 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 (Aug 9, 2019)

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 (Aug 9, 2019)

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 (Aug 9, 2019)

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

Thanks


----------



## billszysz (Aug 9, 2019)

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 (Aug 10, 2019)

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


----------

