Repeating Un-ordered Source to Table

[chris_k]

Hello,

I am trying to turn a source that repeats column headers but in a none consistent order into a table. (The mess of the list is due to the web source which I have no control over). But I am so far getting nowhere quickly. It always repeats in a consistent number of rows per ID if that helps...

Input Table:

ID	Name	Value
0079e79	Average	0.202
0079e79	Score A	0.846
0079e79	Score B	0.916
0079e79	Score G	0.985
0079e79	Score D	0.443
0079e79	Score C	0.738
0079e79	Score E	0.683
0079e79	Score F	0.495
0079e79	Status	0.476
002e377	Average	0.070
002e377	Score A	0.453
002e377	Score B	0.447
002e377	Score G	0.765
002e377	Score D	0.788
002e377	Score C	0.143
002e377	Score E	0.987
002e377	Score F	0.285
002e377	Status	0.236
0061dd2	Status	0.140
0061dd2	Average	0.654
0061dd2	Score A	0.412
0061dd2	Score B	0.647
0061dd2	Score E	0.186
0061dd2	Score F	0.633
0061dd2	Score D	0.774
0061dd2	Score C	0.928
0061dd2	Score G	0.985

Output Table:

ID	Average	Score A	Score B	Score G	Score C	Score D	Score E	Score F	Status
0079e79	0.202	0.846	0.916	0.985	0.738	0.443	0.683	0.495	0.476
002e377	0.070	0.453	0.447	0.765	0.143	0.788	0.987	0.285	0.236
0061dd2	0.654	0.412	0.647	0.774	0.186	0.633	0.928	0.140	0.985

Thank you for any help,

[dilumd · Accepted solution]

Hi,

You can do this with Pivot option in query editor in power BI,

Code,

let
    Source = Excel.Workbook(File.Contents("C:\Users\Dilumd\OneDrive - Help Sheet.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", type text}, {"Name", type text}, {"Value", type number}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Name]), "Name", "Value")
in
    #"Pivoted Column"

[chris_k]

Thank you very much. I had no idea it was that easy!