Forum Discussion
How do I keep my dates when I transpose a query?
You can try the following steps:
1. Click on the Matric column and select Transform -> Unpivot Other columns
2. Click on Matric column again and select Transform -> Pivot Column, selecting Values column as Value
You can try the following steps:
1. Click on the Matric column and select Transform -> Unpivot Other columns
2. Click on Matric column again and select Transform -> Pivot Column, selecting Values column as Value
Hi! Thanks...it almost worked.
Now my dates are in a single column and my KPIs are headers. However, the KPIs number appears now as 0 or 1, and these are not real number. How can I get the real numbers?
Can you check if the last part of the M-code in the formula bar in List.Sum?
= Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Metric]), "Metric", "Value", List.Sum)
I think it worked. While doing the second step (2. Click on Matric column again and select Transform -> Pivot Column, selecting Values column as Value) I went to advanced options and in Aggregate Value Function I selected "Don't aggregate"option.
The last M-code in the formula is: = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Metric]), "Metric", "Value")
It worked perfectly for the majority of the KPI (the actual number came out). However, for some of them the error message was displayed instead of the actual number that is inside the Smartsheet. Any clue why? Is this probably an error on the Smartsheet-PBI communication?
