Dynamically Add New Columns From Datasource Upon Refresh

[Mirithu]

Hi,

I am reading data from an Oracle database table that has a column for every day of the current month. At the start of day, a new column for the previous day is added to the table. As today is the 11th of August, the table looks like below:

Product	DAY01	DAY02	DAY03	DAY04	DAY05	DAY06	DAY07	DAY08	DAY09	DAY10
Mangoes	45	12	5	23	78	7	22	88	45	3
Apples	51	4	67	18	90	55	31	10	0	9

At start of day tomorrow, a new column named DAY11 will be added to the table with respective values.

Now, I am importing this data into PBI Desktop and displaying it using a matrix visual in the same layout but with a custom total column that sums up all the values in each row as below:

Product	DAY01	DAY02	DAY03	DAY04	DAY05	DAY06	DAY07	DAY08	DAY09	DAY10	Total
Mangoes	45	12	5	23	78	7	22	88	45	3	328
Apples	51	4	67	18	90	55	31	10	0	9	335

I would like to have the matrix updated with every new column and corresponding total at the start of each day when refresh occurs. For example, when the new column DAY11 is introduced in the table, when I refresh my matrix, the column DAY11 will also be automatically included and its values added to the total in the Total column.

Is this possible?

[samdthompson · Accepted solution]

Hello, you should unpivot the columns in power query before loading then use the DayNumber column in your column field on the matrix. Your Table will look something like:

Product, Day, Value

Mangoes, 01, 45

Mangoes, 02, 12

Mangoes, 03, 5

...

[Mirithu]

Thank you @samdthompson . The unpivoting in power query has worked perfectly!