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
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?
Can you check what the error says?
I think the error is happening probably because, you gave don't aggregate while pivoting. You could try "Sum" or "Max" instead of "Don't aggregate"