Forum Discussion
Pivot Table with conditional fields
Hi,
I would like to unpivot the summary table below
Table1
| Name | ID-Primary | ID-Secondary | Score 1 (primary) | Score 2 (primary) | Score 3 (secondary) | Type 1 (first) | Type 2 (second) |
A | 100 | 1 | 1 | a | |||
| B | 201 | 1 | b | ||||
| C | 102 | 202 | 1 | 1 | 1 | b | a |
to look like this
Table2
Name | ID | Score 1 (primary) | Score 2 (primary) | Score 3 (secondary) | Type |
A | 100 | 1 | 1 | a | |
| B | 200 | 1 | b | ||
| C | 102 | 1 | 1 | a | |
| C | 202 | 1 | b |
So each row of data has ID as the unique key and should only contain info specific to primary or secondary.
Primary should associate to IDs starting with "1" and "a" type, secondary with "2" and "b".
I know I can do
Table2 =
FILTER(
UNION(
SELECTCOLUMNS(Table1,"Name",[Name],"ID",[ID-Primary]),
SELECTCOLUMNS(Table1,"Name",[Name],"ID",[ID-Secondary])
),
[ID]<>"")
to unpivot IDs for each name, but is there a way to set conditionals for updating the score values and unpivoting the type values to match their respective ID?
Thanks
Hi Anonymous,
You can try to use the following power query codes to achieve your requirement:
Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MACSIAYUA1EiiIrViVZygvCNDKASUCVJMHlnsAFGYCVGSGbAFCUqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"ID-Primary" = _t, #"ID-Secondary" = _t, #"Score 1 (primary)" = _t, #"Score 2 (primary)" = _t, #"Score 3 (secondary)" = _t, #"Type 1 (first)" = _t, #"Type 2 (second)" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ID-Primary", Int64.Type}, {"ID-Secondary", Int64.Type}, {"Score 1 (primary)", Int64.Type}, {"Score 2 (primary)", Int64.Type}, {"Score 3 (secondary)", Int64.Type}, {"Type 1 (first)", type text}, {"Type 2 (second)", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Score 1 (primary)", "Score 2 (primary)", "Score 3 (secondary)", "Type 1 (first)", "Type 2 (second)"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns",each [#"Type 1 (first)"] ,each if [Name]="C" and [Attribute]="ID-Primary" then [#"Type 2 (second)"] else [#"Type 1 (first)"],Replacer.ReplaceText,{"Type 1 (first)"}), #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Type 2 (second)", "Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Type 1 (first)", "Type"}, {"Value", "ID"}}) in #"Renamed Columns"Regards,
Xiaoxin Sheng
2 Replies
Anonymous , My advice would be unpivoted these tables in the power query. Correct column name and values and then Append in power query
https://radacad.com/pivot-and-unpivot-with-power-bi
Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-queryHi Anonymous,
You can try to use the following power query codes to achieve your requirement:
Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MACSIAYUA1EiiIrViVZygvCNDKASUCVJMHlnsAFGYCVGSGbAFCUqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"ID-Primary" = _t, #"ID-Secondary" = _t, #"Score 1 (primary)" = _t, #"Score 2 (primary)" = _t, #"Score 3 (secondary)" = _t, #"Type 1 (first)" = _t, #"Type 2 (second)" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ID-Primary", Int64.Type}, {"ID-Secondary", Int64.Type}, {"Score 1 (primary)", Int64.Type}, {"Score 2 (primary)", Int64.Type}, {"Score 3 (secondary)", Int64.Type}, {"Type 1 (first)", type text}, {"Type 2 (second)", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Score 1 (primary)", "Score 2 (primary)", "Score 3 (secondary)", "Type 1 (first)", "Type 2 (second)"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns",each [#"Type 1 (first)"] ,each if [Name]="C" and [Attribute]="ID-Primary" then [#"Type 2 (second)"] else [#"Type 1 (first)"],Replacer.ReplaceText,{"Type 1 (first)"}), #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Type 2 (second)", "Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Type 1 (first)", "Type"}, {"Value", "ID"}}) in #"Renamed Columns"Regards,
Xiaoxin Sheng
