Forum Discussion

ccsnet's avatar
ccsnet
Frequent Visitor
2 years ago
Solved

Changing Data To Make It Usable For A Graph

Hi all,

I have some data which is not in the best format which I would like to display on a single graph over time however Power BI seems to be struglering to show it more than a single straight line.

Can any body please offer some advice on how I can reformat using PowerQuery this so its useable in a repeatble way as this comes from a dynamic data source ?

NB The first row is how the data is imported and the yellow colums are the one I want to use, the second is an example of where I tried to tidy it up and use it

 

 

Thanks

Terran

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    2 years ago

    A very simple way to transform would be

    Load the table into PQ without headers i.e. your headers will be Column1, Column2...

    Transpose

    Select first column, right click and remove all duplicates

    Transpose

    Promote headers

    Below is the code which gets generated

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Removed Duplicates" = Table.Distinct(#"Transposed Table", {"Column1"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Duplicates"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"

     

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    2 years ago

    A very simple way to transform would be

    Load the table into PQ without headers i.e. your headers will be Column1, Column2...

    Transpose

    Select first column, right click and remove all duplicates

    Transpose

    Promote headers

    Below is the code which gets generated

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Removed Duplicates" = Table.Distinct(#"Transposed Table", {"Column1"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Duplicates"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"

     

  • ccsnet's avatar
    ccsnet
    Frequent Visitor
    2 years ago

    Thanks I will give this a go and come back to this thread.

     

    T