Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Unpivot table with dates

Hi All, I qucikly drew in Excel what I need to achieve in Power Query For now I have a table looking like that: My goal is to transform it to look like that: How to do it in Power Que...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    hi, Anonymous 

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    types = Table.TransformColumnTypes(Source,{{"start_date", type date}, {"end_date", type date}}),
    comb_cols = Table.CombineColumns(types, {"start_date", "end_date"}, (x) => List.Dates(x{0}, Duration.Days(x{1} - x{0}) + 1, #duration(1, 0, 0, 0)), "dates"),
    ws = Table.AddColumn(comb_cols, "ws", each "Workshop"),
    expand = Table.ExpandListColumn(ws, "dates"),
    pivot = Table.Pivot(Table.TransformColumnTypes(expand, {{"dates", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(expand, {{"dates", type text}}, "en-US")[dates]), "dates", "ws")
in
    pivot
AlienSx's avatar
AlienSx
Super User
3 years ago

hi, Anonymous 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    types = Table.TransformColumnTypes(Source,{{"start_date", type date}, {"end_date", type date}}),
    comb_cols = Table.CombineColumns(types, {"start_date", "end_date"}, (x) => List.Dates(x{0}, Duration.Days(x{1} - x{0}) + 1, #duration(1, 0, 0, 0)), "dates"),
    ws = Table.AddColumn(comb_cols, "ws", each "Workshop"),
    expand = Table.ExpandListColumn(ws, "dates"),
    pivot = Table.Pivot(Table.TransformColumnTypes(expand, {{"dates", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(expand, {{"dates", type text}}, "en-US")[dates]), "dates", "ws")
in
    pivot