Forum Discussion
How do I keep my dates when I transpose a query?
Hello everyone,
I have a query that is connected to a Smartsheet file and I am having troubles while transposing my table. Originally, my KPIs are in rows (metrics below) and my month/year are column headers and I wish exactly the opposite: KPIs in columns and month/year in rows (see below).
When I transpose the table, the dates simply disappear! (see below)
How can I transpose witouth loosing the dates?
Thank you,
Barbara Braga
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
7 Replies
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)
