Forum Discussion
calculated column
Hello , does anyone know any solution for this ?
I have a table (as print1 ) , 1 column dimension that has my KPis , and two métrias (Actual and Budget ) as print . I would like to create a new dimension in the column kpi would be the kpi -2 / kpi -1 someone could help me how can I do this to make it as print2
Thank you!
print2
In this scenario, you want to insert a calculated row into your table. However, it's difficult to directly referece cell value for calculation in Power Query. I suggest you split your table into two tables, transpose the columns to calculate the result, then transpose back and merge queries. Please refer to steps below:
1. Split the Print 1 into two tables.
2. Transpose the second table.
3. Since we want to sum id and concatenate string, we can do error handling on row level. Just add calculated column like below:
= Table.AddColumn(#"Transposed Table", "Custom", each try [Column1]+[Column2] otherwise [Column2]&"/"&[Column1])
4. Transpose this table back.
5. Then transpose first table and add calculated column.
= Table.AddColumn(#"Transposed Table", "Custom", each [Column2]/[Column1])
6. Transpose it back and add an index column.
7. Use "Merge Queries" to join the other table together, expand dimension column.
Regards,
2 Replies
In this scenario, you want to insert a calculated row into your table. However, it's difficult to directly referece cell value for calculation in Power Query. I suggest you split your table into two tables, transpose the columns to calculate the result, then transpose back and merge queries. Please refer to steps below:
1. Split the Print 1 into two tables.
2. Transpose the second table.
3. Since we want to sum id and concatenate string, we can do error handling on row level. Just add calculated column like below:
= Table.AddColumn(#"Transposed Table", "Custom", each try [Column1]+[Column2] otherwise [Column2]&"/"&[Column1])
4. Transpose this table back.
5. Then transpose first table and add calculated column.
= Table.AddColumn(#"Transposed Table", "Custom", each [Column2]/[Column1])
6. Transpose it back and add an index column.
7. Use "Merge Queries" to join the other table together, expand dimension column.
Regards,
Thanks, met for what I needed. It would be great to have a simpler way to do these calculations, as exists in other BI tools.
