Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

'Flipping' a horizontal fact table into a vertical fact table

This is a bit of a weird one to explain, but here goes.

 

I'm trying to visualise some timelines using either Timeline by Queryon or Timeline Storyteller.

 

It appears for both of these custom visuals, the data that needs to be provided should be in this format:

 

TABLE A

Case FileMilestoneDate
CS1Query 1 
CS1Query 2 
CS1Query 3 
CS1Query 4 
CS1Query 5 
CS1Info Request 1 
CS1Info Request 2 
CS1Info Request 3 
CS1Closed 
CS2Query 1 
CS2Query 2 
CS2Query 3 
CS2Query 4 
CS2Query 5 
CS2Info Request 1 
CS2Info Request 2 
CS2Info Request 3 
CS2Closed 
CS3Closed 
CS3Query 1 
CS3Query 2 
CS3Query 3 
CS3Query 4 
CS3Query 5 
CS3Info Request 1 
CS3Info Request 2 
CS3Info Request 3 
CS3Closed 

 

However, the data that is being collected by users on the project I am working on is in this format:

TABLE B

Case FileQuery 1Query 2Query 3Query 4Query 5Info Request 1Info Request 2Info Request 3Closed
CS101/01/202110/01/202123/01/202124/01/202131/01/202103/01/202112/01/202125/01/202101/02/2021
CS202/01/202111/01/202124/01/202125/01/202101/02/202104/01/202113/01/202126/01/202102/02/2021
CS303/01/202112/01/202125/01/202126/01/202102/02/202105/01/202114/01/202127/01/202103/02/2021

 

The Case File column contains a unique reference for each case file, so it's also used as a dimension table to form relationships with other sets of data.

 

Is there a way of converting Table B into Table A using powerquery?

Need Help

2 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Select the first column and 'Unpivot other columns'

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      O.M.G. Thank you so much, this works!