Forum Discussion
Repeating Un-ordered Source to Table
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,
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"
2 Replies
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"