Table with dynamic number of columns to display in report

[TomaszKowalik]

Hello,

I have the following situation: a table is auto-generated and stored in the database. This table is dynamic, means the number and order of columns and the data type of the columns varies, depending on the source data.

Now I would like to generate a report in Power BI in order to visualize the table and the contained data. Problem is, that the underlying table structure may change.

My question: is it possible with Power BI to auto refresh such report, means the data structure, depending on the table structure of the source? And that in case of change of the table structure the visualized table in Power BI will be refreshed accordingly, w/o manual adjustment of the report?

[RKM]

If @TomaszKowalik  decides that the reply of @v-xicai  is the answer of his question, then I'm fine and I have absolutely no objection.

But somehow looking at the question and the answer (marked as solution), it doesn't seem like, it is really an answer.

Question is very very clear... there is a table dynamically generating in DB with different list of columns and challenge is how to get Power BI to display it- because all your Table Visual etc. expects Pre Defined list of columns to display.

Honestly I also am still searching for such solution, but seems its not easy. What I potentially can suggest (although not always possible in easy way) is to Pivot that table Dynamically at DB layer and Bring it in the form of <Key Columns, Field, Value> and then use a matrix in Power BI to pull and display those.

[v-xicai]

Hi  @TomaszKowalik ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case.

Best regards

Amy Cai

[v-xicai · Accepted solution]

Hi @TomaszKowalik ,

Is the way getting your data Directquery or Connect Live? If yes, once you make any changes in data source, the report will auto refresh.

You can go through the link to learn more: https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

Best Regards,

Amy

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