I've been struggling with this one for about a week or so. I'm not the best at excel formulas - but have been trying to use queries to fix this issue. I have pasted what I have (first table) and what I want to get to (second table). Is there an easier way to do this? Thanks in advance - I would appreciate any input at this stage.

CodePudding user response：

Basically you want to unpivot your table, but you have 2 header rows. A litte workaround will help:

1. In PowerQuery transpose the table (no headers)
2. Add a new column by concatenating the 1st and 2nd column using a "-"
3. Transpose back
4. Delete the first 2 rows
5. Sort descending and promote the new "concatenated" row as header
6. Unpivot all columns but the first (Date)
7. Split the concatenated column by delimiter "-"

Here's the according M-code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {
            {"Column1", type any}, 
            {"Column2", type any}, 
            {"Column3", type any}, 
            {"Column4", type any}, 
            {"Column5", type any}, 
            {"Column6", type any}, 
            {"Column7", type any}
        }),
    #"Transposed Table" = Table.Transpose(
        #"Changed Type"),
    #"Added Custom" = Table.AddColumn(
        #"Transposed Table", "Custom", each [Column1] & "-" & [Column2]),
    #"Transposed Table1" = Table.Transpose(
        #"Added Custom"),
    #"Removed Top Rows" = Table.Skip(
        #"Transposed Table1",2),
    #"Sorted Rows" = Table.Sort(
        #"Removed Top Rows",{{"Column1", Order.Descending}}),
    #"Promoted Headers" = Table.PromoteHeaders(
        #"Sorted Rows", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(
        #"Promoted Headers",{{"City-Time of Day / Date", "Date"}}),
    #"Changed Type1" = Table.TransformColumnTypes(
        #"Renamed Columns",
        {
            {"Date", type date}, 
            {"LA-Day", Int64.Type}, 
            {"LA-Night", Int64.Type}, 
            {"New York-Day", Int64.Type}, 
            {"New York-Night", Int64.Type}, 
            {"Aukland-Day", Int64.Type}, 
            {"Aukland-Night", Int64.Type}
        }),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
        #"Changed Type1", {"Date"}, "Attribute", "Temperature"),
    #"Split Column by Delimiter" = Table.SplitColumn(
        #"Unpivoted Other Columns", 
        "Attribute", 
        Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"City", "Time of Day"}),
    #"Changed Type2" = Table.TransformColumnTypes(
        #"Split Column by Delimiter",
        {
            {"City", type text}, 
            {"Time of Day", type text}
        })
in
    #"Changed Type2"

CodePudding user response：

Sort of same as peter, a bit more generic for column names and number of columns

Start by loading the data into powerquery. You can directly read CSV files, but I assume here you start with your table instead. Load the table into Powerquery with Data .. from table/range and do NOT check [x] my table has headers. Then paste below into home ... advanced editor... replacing Table1 with your table name in the first row

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], //use your tablename in this step
#"NewNames" = Table.AddColumn(Table.Transpose(Table.FirstN(Source,2)), "Custom", each [Column1]&"-"&[Column2])[Custom],
#"Rename"=Table.RenameColumns( Table.Skip(Source,2), List.Zip( { Table.ColumnNames( Source ), #"NewNames" } ) ),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Rename, {#"NewNames"{0}}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"})
in  #"Split Column by Delimiter"

End the process by renaming the columns (click existing column name and hit F2) and click selecting the columns and setting data types (click select column and use transform .. data type ) which I omitted. Then do File .. Close and Load... **