Any table with dynamic columns turns into static in the model.bim file

[igorplotnikov]

I came across a problem that seems unsolvable at this point. I use .pbip files with version control and classic deployment pipelines to publish reports. The thing is, any dynamic table in power query that has an unknown number of columns in advance and that I retrieve by pivoting becomes static in the data model. So when adding new columns, this table is not enriched because its schema is hardcoded in the model.bim file. An attempt to move the pivoting part from the Power Query level to the DAX level did not work. Maybe you have some ideas what can be done about this and how can truly dynamic tables be implemented in power BI? Thank you for your time in advance.

[igorplotnikov]

@v-rongtiep-msft Do you have any ideas though?

[v-rongtiep-msft]

Hi @igorplotnikov ,

The first thing you need to understand is that the  calculated column is a static value added to a table, calculated row by row when you load the data. In contrast, a measure is a dynamic value that changes based on what you’re looking at in a report, calculated on the fly within the filter context of a visual.

Please learn more about columns and measures.

Understanding Measures vs Calculated Columns in Power BI (gorilla.bi)

Then you can  create dynamic tables in Power BI using DAX. I have found a document about creating dynamic tables.Dynamically Create Tables in Power BI with DAX Functions (mssqltips.com)

There is also a similar post, please refer to.

Solved: Create Dynamic Matrix Table - Microsoft Fabric Community

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[igorplotnikov]

@v-rongtiep-msft Here is the deal: I have a table that initially looks like this (the screenshot). I want it to be pivoted by column_name and filled with values respectively. I don't know in advance how many columns this pivoted table will have, and another refresh can bring even more of them. So I cannot specify their names explicitly in a DAX formula like you suggested.

And as I mentioned, being pivoted in Power Query and refreshed once, the table gets its schema hardcoded and never changed anymore, even if new columns from new records are created over time.

Will be thankful for any valid ideas.