Stuck on Date merge

[SMid]

Hi all,

I’m running into an issue that I hope that you can help me with. I am trying to merge two tables based on four conditions: Date, Customer Price Group, SSO and Item Category. Both tables have over 100k rows.

One table has sales data based on individual dates:

               Posting Date – Customer Price Group – SSO – Item Category Code

The other has backcondition data with Start date and End date:

               S date – E Date – Customer Price Group – SSO - Item Category Filter

My first thought was to do just do a simple merge. Change S Date and E date to numerical type, creating a list and then expanding the list to new rows. Then merge on newly created dates column and the other categories that have to match.

#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"S Date", Int64.Type}, {"E Date", Int64.Type}}),

                #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each {[S Date]..[E Date]}),

                #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),

 #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),

Unfortunately, my system gets stuck when performing the merge, I assume because there are too many rows.

So, then I tried to merge on the date range by using select rows, thus not having to expand S Date and E Date to include all dates like this:

    #"Merge" = Table.AddColumn(#"Removed Columns1", "Percentage",

                            (S)=>  Table.SelectRows(#"Customer Backconditions, DE - Group base",

(P)=> P[Customer Price Group]= S[Customer Price Group] and P[SSO]= S[SSO] and P[Item Category Filter]= S[Item Category Code] and S[Posting Date]>= P[S Date] and S[Posting Date]<P[E Date]) ),

With this option the merge worked but expanding the column with the value I need did not. I also realised that it would not show each possible value if there was more than one valid row to merge on.

Then the last thing I could think of was to once again expand the S Date and E Date to show all dates between, then merge all columns of the backcondition data, and convert this merged column to a list.

#"Merged Columns" = Table.CombineColumns(#"Changed Type3",{"Dates","Customer Price Group", "Item Category Filter", "SSO", "Percentage"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),

               #”Merged” = #"Merged Columns"[Merged]

Then add column to search the list for the necessary conditions

#"Added Custom1" = Table.AddColumn(#"Changed Type2", "Category", each let Filter1 = [Customer Price Group], Filter2 = [Posting Date], Filter3 = [Item Category Code], Filter4 = [SSO] in List.Select(TestList, each Text.Contains( _, Filter1) and Text.Contains( _, Filter2) and Text.Contains( _, Filter3) and Text.Contains( _, Filter4)))

But once again to expand the column to show the value I need takes forever.

I am running out of ideas now, does anyone have a better idea on how to do this?

Thanks in advance.

[KNP]

I think the first two options you've mentioned should work fine.

You've likely seen these articles already but just in case...

https://exceed.hr/blog/merging-with-date-range-using-power-query/

https://radacad.com/dates-between-merge-join-in-power-query

Depending on which option, disable load of queries that don't need to load or use Table.Buffer as mentioned in the 'exceed.hr' blog above.

[SMid · Accepted solution]

Well I found a work around it seems.

I decided to join on just the categories and bring over the Start and End dates of the backconditions to the sales table that way. Conditional column on those dates and posting dates and voila I have what I need. Not a pretty solution, but I guess it will work for now.

Thanks for the input.

[KNP]

No problem.

Glad you have a working solution. 👍

[SMid]

Hi, thanks for your reply. I agree it should work in theory, but it doesn't. I tried to load the query through the night, but this morning it was still stuck on 'creating connection in model'. My CPU and memory were both running at 95%, so I guess it must be an efficiency thing? I have all unneccessary queries on do not load. Only thing I haven't tried is Table.buffer, as in my experience it is a bit hit or miss. Will get back to you after I try it.