Forum Discussion
Power Query - full join or create combine list
Please can you confirm why you wish to do this. Most of the time, there is another method to achieve the same goal.
To answer you question, you could pivot, then duplicate and unpivot... but the method is not ideal
You could create a column column on the date table which generate your list of numbers, then all you would need to do is expand them (this would then create duplicates of your dates as required)
Regards,
Mathew
Thank you guys.
I solved my problem with solution from 101Mathew . I created list List.Dates and then I added column Table.AddColum. It works fine.
...
Zdroj = Table.FromValue(List.Dates(DateTime.Date(dates_from), interval, #duration(1, 0, 0, 0))), #"Pridané vlastné" = Table.AddColumn(Zdroj, "numbers", each List.Numbers(0, 25)),
...
But I have one new problem with Table.Pivot. I want to order columns in pivot table, but I have to change column first from number to text and then add "0" before number if it is necesary. I don't know how to do it.
This is changing type code, which works fine:
#"Zmenený typ" = Table.TransformColumnTypes(#"Pridané vlastné1",{{"numbers", type text}}),Can you help me insert code instead of ????? which add 0 .... I think it is something like Text.PadStart
This is all code:
let
S1 = Excel.CurrentWorkbook(){[Name="start"]}[Content],
S2 = Excel.CurrentWorkbook(){[Name="end"]}[Content],
dates_from = S1{0}[start],
dates_to = S2{0}[end],
interval = Number.From( dates_to - dates_from) +1,
Zdroj = Table.FromValue(List.Dates(DateTime.Date(dates_from), interval, #duration(1, 0, 0, 0))),
#"Pridané vlastné" = Table.AddColumn(Zdroj, "numbers", each List.Numbers(0, 25)),
#"Rozbalené Vlastné" = Table.ExpandListColumn(#"Pridané vlastné", "numbers"),
#"Zlúčené dotazy" = Table.NestedJoin(#"Rozbalené Vlastné",{"Value", "numbers"},read_data,{"Date", "Hours"},"read_data",JoinKind.LeftOuter),
#"Rozbalené read_data" = Table.ExpandTableColumn(#"Zlúčené dotazy", "read_data", {"Value"}, {"read_data.Value"}),
#"Pridané vlastné1" = Table.AddColumn(#"Rozbalené read_data", "weekname", each Date.DayOfWeekName([Value])),
#"Zmenený typ" = Table.TransformColumnTypes(#"Pridané vlastné1",{{"numbers", type text}}),
?????
#"Kontingenčný stĺpec" = Table.Pivot(Table.TransformColumnTypes(#"Zmenený typ", {{"numbers", type text}}, "sk-SK")
, List.Distinct(Table.TransformColumnTypes(#"Zmenený typ", {{"numbers", type text}}, "sk-SK")[numbers])
, "numbers"
, "read_data.Value")
in
#"Kontingenčný stĺpec"And picture of data table:
It is final code works for me.
Thank you all.
let
S1 = Excel.CurrentWorkbook(){[Name="start"]}[Content],
S2 = Excel.CurrentWorkbook(){[Name="end"]}[Content],
dates_from = S1{0}[start],
dates_to = S2{0}[end],
interval = Number.From( dates_to - dates_from) +1,
Zdroj = Table.FromValue(List.Dates(DateTime.Date(dates_from), interval, #duration(1, 0, 0, 0))),
#"Pridané vlastné" = Table.AddColumn(Zdroj, "numbers", each List.Numbers(0, 25)),
#"Rozbalené Vlastné" = Table.ExpandListColumn(#"Pridané vlastné", "numbers"),
#"Zlúčené dotazy" = Table.NestedJoin(#"Rozbalené Vlastné",{"Value", "numbers"},read_data,{"Date", "Hours"},"read_data",JoinKind.LeftOuter),
#"Rozbalené read_data" = Table.ExpandTableColumn(#"Zlúčené dotazy", "read_data", {"Value"}, {"read_data.Value"}),
#"Pridané vlastné1" = Table.AddColumn(#"Rozbalené read_data", "weekname", each Date.DayOfWeekName([Value])),
#"Zmenený typ" = Table.TransformColumnTypes(#"Pridané vlastné1",{{"numbers", type text}}),
#"Pridané vlastné2" = Table.AddColumn(#"Zmenený typ", "stlpce", each Text.PadStart([numbers],2,"0")),
#"Odstránené stĺpce" = Table.RemoveColumns(#"Pridané vlastné2",{"numbers"}),
#"Kontingenčný stĺpec" = Table.Pivot(Table.TransformColumnTypes(#"Odstránené stĺpce", {{"stlpce", type text}}, "sk-SK")
, List.Distinct(Table.TransformColumnTypes(#"Odstránené stĺpce", {{"stlpce", type text}}, "sk-SK")[stlpce])
, "stlpce"
, "read_data.Value"),
#"Odstránené ostatné stĺpce" = Table.SelectColumns(#"Kontingenčný stĺpec",{"Value", "weekname", "00", "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24"})
in
#"Odstránené ostatné stĺpce"