How to handle refresh PBI if all columns has dates as headers as dates keep changing

[Suhil]

Data is like shown above. How to handle such data as dates will change every day. It is last 30days data which we display

[HotChilli · Accepted solution]

You will need to edit/delete steps in the query transformation to remove anything which is related to the names of the 'date' columns (because they will cause a problem when the next set of data is imported).

So, The first step will import the data.

Then there will probably be a 'Changed Type' step - you can delete anything to do with 'date' columns and leave anything to do with 'fixed' columns.

You will then select the 'fixed' columns and 'Unpivot Other Columns'. 

And then the dates will be in one column so you can set the datatype to date.

This makes the query steps generic.

[Suhil]

Thank you, i was able to do what you suggested, but i have another issue in this . 

Current view is as show below. Is it possible to make it look in descending order( latest date first) otherwise i will have to scroll towards right and see latest date.

[HotChilli]

Is that a matrix visual? I think you have to make a custom column and use 'sort by column' feature 

[Suhil]

Can you elaborate what exactly we need in custom column

[Suhil]

Thanks for suggestion. I tried to unpivot and do like you suggested but still getting error as previous dates are getting deleted in source file on every refresh

[HotChilli]

This is a useful format for a visual but it's not good for storage format or for simplifying the writing of measures.

--

Typically there will be an ID column for each row and we Unpivot all the date columns.  This solves the problem of new columns as well