Oldest date from column as Column header

[FredDeb]

Hi, 

On a daily base I have e reporting file with the number of planned installs for the next 30 days.

That 30 day window changes every day (+1 day).

I would like to import that data from every day in a powerbi report.

I would like to see the evolution of the planned installs day by day.

For example:

The daily file of 05/01/2023 tells me that there are 500 installs planned for 20/01/2023 and 510 for 21/01/2023

The daily file of 06/01/2023 tells me that there are 480 installs planned for 20/01/2023 and 520 for 21/01/2023

In one way or another that should create some kind of waterfall table. But I have no idea how to visualize that in PowerBi.

I have this kind of dataset for the moment: 

From "column2" until the last column, the column header should be the oldest date in that specific column.

This is what my final table should look like:

Who can help me?

Thanks in advance.

[ToffeeMarkWork]

I think the installation date should be a column  So you should have reporting date, and installation date as columns, then a values column.  that would allow you to make your table as a pivot table in excel or power bi.  

I was able to recreate your desired table by taking the data unpivoting, filtering on dates first to get the date of each column, then filtering on value to get the value of each column, then merged date&column number from the first tabel with date & column number from the second table.  Load the data then make a pivot table with report date as rows, instalation date as columns and values as values.

I only did 10/10/2022 and 13/10/2022 but you could filter dates from values by contains "/" or does not contain "/" .  There may be a more elegant solution but I ended up with 

Be better if the source data was set up differently tbh

[FredDeb]

Hi @ToffeeMarkWork , 

Thanks for your reply. This helps. When I do the unpivot of the columns, I get: 

How do I get the values and the dates from the value column in 2 separate columns?

Thanks in advance...

[ToffeeMarkWork · Accepted solution]

I filtered by date so could use contains "/" to make the date table.  Then I copied the query and filtered by does not contain "/" to get the values, then I merged by date AND column number (attribute) to get the final table.