Pivot Table with conditional fields

[samwrite]

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

[v-shex-msft · Accepted solution]

Hi @samwrite,

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

[amitchandak]

@samwrite , 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-query