Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SMid
Frequent Visitor

Stuck on Date merge

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.

1 ACCEPTED SOLUTION
SMid
Frequent Visitor

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.

View solution in original post

4 REPLIES 4
KNP
Super User
Super User

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.

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
SMid
Frequent Visitor

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.

No problem.

Glad you have a working solution. 👍

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
SMid
Frequent Visitor

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors