Forum Discussion
Helper IPower Query removes first blank column of the table automatically
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...
9 Replies
HasanKI 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-msftCommunity Support
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,
KellyDid I answer your question? Mark my post as a solution!
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
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.
