Power Query removes first blank column of the table automatically

[HasanK]

I have an issue in Power Query,

Weekly reports are comming, each reports first column is BLANK. When I created a query first, Power Query showed this blank column and I defined all steps according to this. Then next week reports came but Power Query does not show first BLANK column any more and tries to process all steps without it and consequently query gets error, because Power query detects BLANK column in 2nd week report. How can it be fixed, I will appriciate...

[Naryan]

For anyone who stumbles onto this thread in the future. You can add a custom column in which you promote the headers of the tables and then expand this column. This way it is not important if some tables have an empty first column and some don't. Since the table headers will be used to append the tables.

[watkinnc]

All you need to do is: on the table with your table column, before you expand the columns, add a columns with the column counts of each table. Then sort that column in descending order. This way, your transform file will apply the transforms to the table with the most columns, and any tables with less columns will still follow this order, and you'll keep all of your columns. 

columns_count = Table.AddColumn(Name of prior step, "Count", each Table.ColumnCount([Transform File]))

Then sort, then expand, all set!

--Nate

[HasanK]

Thanks @Fowmy ,

Yes it is but this is automatically sent in this format... Your mean that needs manual touch. Also, it is strange, PowerQuery should not act like that without permission.

BR

[Fowmy]

@HasanK 

It is better to format the data in the Excel File as a Table to avoid such things.

[HasanK]

I realised something, it looks a bug or something... I remembered that I opened the file and delete 29/6/2021 and 30/6/2021 because they were in June, and company sends monthly report end of the month (June), therefore first week of July reports also contained 29 and 30 of June.. I just deleted those two columes, A column still there is but interestingly after this interference Power Query started not to recognise BLANK "A" Column. When I copy the original file, it is ok Power QUery reconises BLANK A Column, but if you interfere the file some how, query is broken  

[v-kelly-msft]

Hi @HasanK ,

I found that there is a "remove" step in your top 3 queries.So would you pls check about it?Seems that the remove isnt done automatically.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[HasanK]

Yes that step removes some columns including Column1 (normally it must be BLANK one) but BLANK column is not exist therefore it removes Column2 as it is assuming Column1 therefore query is broken...

[gabotuit2]

Hello, did you ever find a solution to this issue?

[HasanK]

Yes I did, I put a character into A1 manually, it is solved.

Thanks