Forum Discussion
calculated column
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,
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.