Data source with different number of columns

[andrearuggeri]

Hello,

I have this tricky scenario and please I need some advice to find the best solution.

I need to build a report with some visuals based on an MS Excel data source I get from an online tool we use to collect data.

Every week we need to download the new data and update the PBI report with the new data source

The problem is that, depending on the collected data, the number of columns may be different, there are some columns that one week are in the report and the other week are missing.

What is the best approach to manage the whole thing, according to your experience?

Thanks a lot!

[PhilipTreacy]

Hi @andrearuggeri 

Honestly the best thing would be to make sure that you have the same number ofcolumns with the same names every time you get new data.

Otherwise you may end up with transformations in Power Query that refer to column names that do not exist or other such problems.

Depending on exactly what you are trying to do in PBI, you may or may not be able to deal with such issues but really avoiding them in the first place is the best thing.

Regards

Phil

[andrearuggeri]

This is exactly my problem, I can't grant the exact number of columns every time...

I tried with an append query and it correctly adds new columns, but I frequently get format issues.

I was wondering if an easier approach exixts, since I had no such problems with other tools like Zoho, where I can append data and everything is managed automatically.

Thanks a lot