Pivot Table with conditional fields

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