Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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...
Need Help
  • dilumd's avatar
    dilumd
    9 years ago

    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"
dilumd's avatar
dilumd
Impactful Individual
9 years ago

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"
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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