Table: Number/Names/Order of columns changes over time

[SokMunki]

Hi all! I'm moving a table from an Excel deliverable to a Power BI deliverable. The table is a Direct Query link to a SQL view. Trick is, the view has 400+ columns that can vary depending on the products customers own on a given day. For example, one day might have:

Cust ID

CustName

etc.

Product A1

Product A2

Product A4

Product A5

Product B2

Product B4

but the next day might have:

Cust ID

CustName

etc.

Product A1

Product A4

Product A7

Product B2

Product B6

...for 400+ Products. 

Is there a way to have Power BI just show all available columns in the Table visualization based on the columns in the view?

Thanks very much for your help! 

[Anonymous · Accepted solution]

Hi @SokMunki ,

According to your statement, I know that you use Direct Query connection mode to get data. As far as I know, Power BI will load all metadata from your data source into data model. Metadata contains all data about columns in your data source. I think you can see all columns in Power BI.

For reference:

Using DirectQuery in Power BI - Power BI | Microsoft Learn

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SokMunki]

Sorry about the lack of borders between cells in the HTML tables up there, by the way. Can't seem to get that to work.