cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
RaptorFox
Frequent Visitor

Convert dynamic amount of date rows to columns

Dear community

 

I have multiple CSV blobs in a particular storage container. Those are pulled from our backup system once a week for the last seven days. Each row indicates the system to be backed up, and then n-amount of date-named columns show if the backup was successful on this particular date (1) or not (0).

 

situation_display.jpg

 

I need to be able to use date slicers in my dashboard and provide an easy overview. Therefore, i need one row per system per date for the dynamic amount of dates (columns) like this:

 

situation_display_target.jpg

 

I now tried 3 hours, and need to give up. How can i do this conversion in the easiest way?

 

Thank you very much for any help! 

1 ACCEPTED SOLUTION
AlexanderPrime
Solution Supplier
Solution Supplier

Hi there. 

Have you tried in Power Query selecting all the columns except the System Column and then using the "Unpivot Columns" function?

Doing that should give you an output like this:

 

AlexanderPrime_0-1656053074013.png

Then you'd just need to rename the 2 columns it created and you should be good to go. 


Please note - I am not affiliated with Microsoft, I'm just an end user like yourself.
Just a regular guy doin' Data Science.

If my post has helped you, please don't forget to thumbs up or click "Accept as solution" if it solved your problem!

View solution in original post

2 REPLIES 2
RaptorFox
Frequent Visitor

Thank you very much, it worked perfectly! 

AlexanderPrime
Solution Supplier
Solution Supplier

Hi there. 

Have you tried in Power Query selecting all the columns except the System Column and then using the "Unpivot Columns" function?

Doing that should give you an output like this:

 

AlexanderPrime_0-1656053074013.png

Then you'd just need to rename the 2 columns it created and you should be good to go. 


Please note - I am not affiliated with Microsoft, I'm just an end user like yourself.
Just a regular guy doin' Data Science.

If my post has helped you, please don't forget to thumbs up or click "Accept as solution" if it solved your problem!

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors