Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HasanK
Helper I
Helper I

Power 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...

image.png

HasanK_0-1626084939468.png

HasanK_0-1626085693530.png

 

 

9 REPLIES 9
Naryan
New Member

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
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
HasanK
Helper I
Helper I

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
Super User
Super User

@HasanK 

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



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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  

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!

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...

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

 

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

Thanks

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors