Power Query - Generate table with lookups and blanks

Question

Hello Fabric Community,&nbsp;I've been using Power BI extensively but as I go into the dataverse I need to now do some of my transformations and coding in Power Query instead. I am making up a similar scenario and would like to see how to complete my desired table generation. Ok first, I have the following table I getActivity IDCode TypeCode Value1ColorRed1SizeM2ColorRed3SizeL4Color&nbsp;Blue4Weight154SizeS5SizeS6LocationCity&nbsp;My goal is to transform this table into the following (also as a note this is about 10 rows, my actual data has 10000+ rows with 20+ Code Types):Activity IDColorSizeWeight&nbsp;Location1RedM(blank/null)(blank/null)2Red(blank/null)(blank/null)(blank/null)3(blank/null)L(blank/null)(blank/null)4BlueS15(blank/null)5(blank/null)S(blank/null)(blank/null)6(blank/null)(blank/null)(blank/null)City&nbsp;So far I know I can use List.Distinct to extract both the distinct activity ID and the CodeTypes. I can also use Table.FromList to start turning into these tables. I'm struggling on combining the tables (one from Activity ID and one from Code Types) and also filling out this eventual blank table as shown above&nbsp;I would appreciate any help/hint on this,Thanks,Alvin

anonymous · Accepted Answer

Hi&nbsp;AlvinLy&nbsp;,
&nbsp;
check the [Code Type] and pivot the column:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLOz8kvAtJBqSlKsToQseDMqlQg5QsWMMKiyBihyAcsYIKkyCmnNBUuGJ6amZ5RAmQYmsLFoDqDwQKm6AJmIEPzkxNLMvPzQKZmllQqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Activity ID" = _t, #"Code Type" = _t, #"Code Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Activity ID", Int64.Type}, {"Code Type", type text}, {"Code Value", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Code Type"]), "Code Type", "Code Value")
in
    #"Pivoted Column"

Best Regards,GaoCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;to help the other members find it more quickly.If I misunderstand your needs or you still have problems on it, please feel free to let us know.&nbsp;Thanks a lot!
How to get your questions answered quickly&nbsp;--&nbsp;&nbsp;How to provide sample data in the Power BI Forum&nbsp;--&nbsp;China Power BI User Group

Forum Discussion

Power Query - Generate table with lookups and blanks

1 Reply

Related content

Lookup help

Generating a Lookup Table Help

Generating Missing Data

Blank Page - Embedded Report

Generating link

Activity ID	Code Type	Code Value
1	Color	Red
1	Size	M
2	Color	Red
3	Size	L
4	Color	Blue
4	Weight	15
4	Size	S
5	Size	S
6	Location	City

Activity ID	Color	Size	Weight	Location
1	Red	M	(blank/null)	(blank/null)
2	Red	(blank/null)	(blank/null)	(blank/null)
3	(blank/null)	L	(blank/null)	(blank/null)
4	Blue	S	15	(blank/null)
5	(blank/null)	S	(blank/null)	(blank/null)
6	(blank/null)	(blank/null)	(blank/null)	City