Forum Discussion

VincenzoChean's avatar
VincenzoChean
Helper I
4 years ago
Solved

Power Query help

Hi,    Does anyone know how i can convert this using power query or from excel.  The header is on the 1st column, it organize in a weekly fashion.  The spreadsheet was not designed by me. But i n...
Need Help
  • ImkeF's avatar
    ImkeF
    4 years ago

    Good, so you add the category-field to the unpivot others step:

    // Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows2" = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows2", "Week", each if Text.StartsWith([Column1], "Week ") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Week"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Week 1", "Week 1_1", "category"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each not Text.StartsWith([Week 1], "Week ")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Value] <> "")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows1",{{"Week 1", "Row"}, {"Week 1_1", "Week"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Value", type number}, {"category", type text}})
in
    #"Changed Type1"

     

ImkeF's avatar
ImkeF
Community Champion
4 years ago

Hi VincenzoChean ,
please paste the following code into the advanced editor and follow the steps:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows2" = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows2", "Week", each if Text.StartsWith([Column1], "Week ") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Week"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Week 1", "Week 1_1"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each not Text.StartsWith([Week 1], "Week ")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Value] <> "")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows1",{{"Week 1", "Row"}, {"Week 1_1", "Week"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Value", type number}})
in
    #"Changed Type1"

 

File also attached.

 

  • VincenzoChean's avatar
    VincenzoChean
    Helper I
    4 years ago

    Hi 

     

    Thanks for assisting. i forgot there's another column that has to go onto it. 

    Can you please assist again ?

     

    Power query help 2 

     

    Secondly, do i have to add to 'Data model' before i can use the power query editor ? 

    I tried going into advance editor but there's no data to select. 

     

    Thanks 

     

     

    • KT_Bsmart2gethe's avatar
      KT_Bsmart2gethe
      Impactful Individual
      4 years ago

      Hi VincenzoChean ,

       

       

      Below transformation code should be dynamic enough to turn the data into structured data formate:

       

      let

       

      //Get Source
      Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

      //Unpivot other column other than the first one (i.e. week1,2, column)

      #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),

      //Add conditional column to get "Week", then fill down

      #"Added Custom1" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Text.StartsWith([Column1],"Week") then [Column1] else null),
      #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom"}),

      //Filter for only the header rows

      ColumnTbl = Table.SelectRows(#"Filled Down", each Text.StartsWith([Column1],"Week")),

      //Filter for all row except header rows

      NonColumnTbl = Table.SelectRows(#"Filled Down", each not Text.StartsWith([Column1],"Week")),

      //Join the main table (the content without header) with header table to get table

      #"Merged Queries" = Table.NestedJoin(NonColumnTbl, {"Custom", "Attribute"}, ColumnTbl, {"Custom", "Attribute"}, "NonColumnTbl", JoinKind.LeftOuter),
      #"Expanded NonColumnTbl" = Table.ExpandTableColumn(#"Merged Queries", "NonColumnTbl", {"Value"}, {"Date"}),

      //Rename header (rename to whatever name you want)

      #"Renamed Columns" = Table.RenameColumns(#"Expanded NonColumnTbl",{{"Column1", "Measure / Title"}, {"Custom", "Week Number"}}),

      //Remove access columns
      #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"})

       


      in
      #"Removed Columns1"

       

       

       

      Regards

      KT