cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
palo173 Regular Visitor
Regular Visitor

Power Query - full join or create combine list

Hi,

I have to list with generated values in Power Query:

"List.Numbers(0, 25)"

"List.Dates(#date(2019, 10, 27), 3, #duration(1, 0, 0, 0))"

 

and I want to full join it as in the pictures below.

Or maybe generate final list, where it will be to combine already.

 

Thank you

 

image.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
101Mathew Regular Visitor
Regular Visitor

Re: 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)

 

Screen Grab.PNG

Regards,

Mathew

View solution in original post

7 REPLIES 7
101Mathew Regular Visitor
Regular Visitor

Re: 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)

 

Screen Grab.PNG

Regards,

Mathew

View solution in original post

palo173 Regular Visitor
Regular Visitor

Re: Power Query - full join or create combine list

I am not against another method. I welcome better way than mine.

I choose this method because It seemed to be the simplest method for me, but I don't have much experience with Power Query. 

 

My goal:

I have a users who give me a date range, date_from and date_to. I have to create table with each date from this range in first column and with numbers from 0 to 24 in second column for each date (for example from 27.10.2019 to 29.10.2019)

Next I need to left join data from ODataFeed to my created table above.

And next I need to create pivot table from these joining data. In row with dates and in columns with numbers from 0 to 24.

 

This is my goal. 

rsmoreno New Member
New Member

Re: Power Query - full join or create combine list

Seems like you need a crossjoin.

 

Create a dummy column in each of the tables. Give the same value to all the rows of the dummy column in both tables (e.g.: "1"). Inner joinjoin tables on the dummy columns. Voila: you have the Cartesian product of the tables.

Super User
Super User

Re: Power Query - full join or create combine list

Hi,

 

Refer to the numbers table in this PBI file.

 

Hope this helps.

101Mathew Regular Visitor
Regular Visitor

Re: Power Query - full join or create combine list

@palo173 @Ashish_Mathur @rsmoreno

 

My orginal power query solution, will work fine, as noted with the screen shot.  you can use excel files, to pull the users dates into your query (or a paramenter setting).

 

After go to add column, and click on custom column then =List.Number(0,25) this produces a list embeded into your dates... expand the list you duplicate the dates with the numbers needed.

 

(you could also have this number range set as a paramenter is needed)

 

Regards,

Mathew

palo173 Regular Visitor
Regular Visitor

Re: Power Query - full join or create combine list

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:

image.png

 

 

Highlighted
palo173 Regular Visitor
Regular Visitor

Re: Power Query - full join or create combine list

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"

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 337 members 3,269 guests
Please welcome our newest community members: