topic Re: Power Query - Bridge table - Weeknum, Date in Power Query

Power Query - Bridge table - Weeknum, Date

LorenzoMolteni — Thu, 13 Aug 2020 14:18:02 GMT

Hi all,

I'm new to Power Query and looking for a solution to solve a small issue I have with my report.

I'm looking into sales data. My goal to is to get data from two different sources (Selection of weekly products & Daily Sales) - I do it by automatically import data from given folders - and create a moving dashboard on excel with powerpivot.

My two sources are built like this

Selection of weekly products

WEEK	Upload date	Product No.	Brand	Category
32	09.08.2020	P001	Gucci	shoes
32	09.08.2020	P002	Saint Laurent	bags
32	09.08.2020	P003	Gucci	clothing

WEEK	Upload date	Product No.	Brand	Category
33	16.08.2020	P009	Gucci	shoes
33	16.08.2020	P010	Valentino	bags
33	16.08.2020	P011	Gucci	clothing

Daily Sales

Sale Date	No.	QTY	Revenue
10.08.2020	P001	1	100
10.08.2020	P003	4	250

Sale Date	No.	QTY	Revenue
16.08.2020	P001	2	200
16.08.2020	P010	3	600

What I want to achieve on my pivot in excel - by creating connections - is that when I filter by i.e. WEEK 32 I get sale numbers only from that week and not from the next weeks (since the product will still be sold even after those days - but I'm only interested in the sales of the first week). The problem is occurring mainly due to the fact that I'm not able to create bridge tables because from one source I get the Weeknum and from the other one I get the Sale Date.

Please let me know if something is unclear. I thank you in advace for your help!

Best,

Lorenzo

Re: Power Query - Bridge table - Weeknum, Date

edhans — Thu, 13 Aug 2020 15:20:42 GMT

I do not think you need a bridge table. You need a Date table, with at least 2 fields. (Should have a few dozen, but these two are critical for you.)

The date. Obviously. You'll mark the table as a date table in Power BI or Power Pivot, then this field as the date field.
A week/year column. So 202001, 202002, etc. through 202052 or 202053 however it works out. You can do this by using this formula:

Date.Year([Date]) * 100 + Date.WeekOfYear([Date])

You need to add a new Week/Year column to your table that only has the week number. You need a date, or you can use this formula, though it is a bit of a hard-code.

2020 * 100 + Date.WeekOfYear([Date])

Then use the date or week or year/week field from your date table, and relate the Date[Date] field to your sales date, and the Date[Year Week] to your Year/Week num field in the other table.

Here is a fairly comprehensive date table with this info, and below a link to show you how to make it dynamic.

let Source = {Number.From(#date(2020,1,1))..Number.From(#date(2020,12,31))}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}), #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type), #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type), #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text), #"Added Short Month Name" = Table.AddColumn(#"Inserted Month Name", "Short Month Name", each Text.Start([Month Name],3), type text), #"Added Month Year" = Table.AddColumn(#"Added Short Month Name", "Month Year", each [Short Month Name] & " " & Text.From([Year]), type text), #"Added Month Year Sort" = Table.AddColumn(#"Added Month Year", "Month Year Sort", each [Year]*100 + [Month], Int64.Type), #"Inserted Quarter Number" = Table.AddColumn(#"Added Month Year Sort", "Quarter Number", each Date.QuarterOfYear([Date]), Int64.Type), #"Inserted Quarter" = Table.AddColumn( #"Inserted Quarter Number","Quarter", each "Qtr " & Text.From([Quarter Number]), type text), #"Inserted Quarter Year Sort" = Table.AddColumn(#"Inserted Quarter", "Quarter Year Sort", each [Year] * 10 + [Quarter Number], Int64.Type), #"Inserted Quarter Year" = Table.AddColumn(#"Inserted Quarter Year Sort", "Quarter Year", each "Q" & Text.End([Quarter],1) & " " & Text.From([Year]), type text), #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter Year", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type), #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type), #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([Date]), Int64.Type), #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type), #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type), #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([Date]), type text), #"Added IsFuture Boolean" = Table.AddColumn(#"Inserted Day Name", "IsFuture", each [Date] > DateTime.Date(DateTime.LocalNow()), type logical), #"Added IsInCurrentWeek" = Table.AddColumn(#"Added IsFuture Boolean", "IsInCurrentWeek", each Date.IsInCurrentWeek([Date]), type logical), #"Added IsInCurrentMonth" = Table.AddColumn(#"Added IsInCurrentWeek", "IsInCurrentMonth", each Date.IsInCurrentMonth([Date]), type logical), #"Added IsInCurrentQuarter" = Table.AddColumn(#"Added IsInCurrentMonth", "IsInCurrentQuarter", each Date.IsInCurrentQuarter([Date]), type logical), #"Added IsInCurrentYear" = Table.AddColumn(#"Added IsInCurrentQuarter","IsInCurrentYear", each Date.IsInCurrentYear([Date]), type logical), #"Added Year Week" = Table.AddColumn(#"Added IsInCurrentYear", "Year Week", each Date.Year([Date]) * 100 + Date.WeekOfYear([Date]), Int64.Type) in #"Added Year Week"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

Creating a Dynamic Date Table in Power Query

Re: Power Query - Bridge table - Weeknum, Date

LorenzoMolteni — Thu, 13 Aug 2020 16:25:50 GMT

Hi edhans

Thanks for your help with this. Really much appreciated.

What I still can't understand is how i can relate Date[Year Week] to the new Year/Week num in my source table since each column contains duplicate value and PowerPivot doesn't allow me to do that.

How can I overcome this?

Thank you!

Lorenzo

Re: Power Query - Bridge table - Weeknum, Date

Pragati11 — Thu, 13 Aug 2020 16:38:34 GMT

HI LorenzoMolteni ,

Try creating a relationshipp between your 2 tables on Product No.

Thanks,

Pragati

Re: Power Query - Bridge table - Weeknum, Date

LorenzoMolteni — Thu, 13 Aug 2020 16:42:06 GMT

Hi Pragati11

That relationship is already created but doesn't help to solve the above metioned issue on WeekNum and Date.

Best,

Lorenzo

Re: Power Query - Bridge table - Weeknum, Date

edhans — Thu, 13 Aug 2020 16:55:21 GMT

ACK! You are correct. That is many to many. You'll need to convert that week number to a date. This will do that:

List.Min( { #date(2020+1,1,1), Date.AddDays( #date(2020,1,1), -Date.DayOfWeek( #date(2020,1,1), Day.Monday )+[Week of Year] * 7 ) } )

You'll need to play with it and make sure it conforms to your week. My example starts on a Monday. You can change at at to Day.Sunday, Day.Wednesday, or whatever.

Then connect the Date[Date] field (unique) to this new pseudoweek day field in your sales table.

And I've hardcoded the 2020. You should put in a valid year field so it doesn't stop working in 4 months.

Re: Power Query - Bridge table - Weeknum, Date

LorenzoMolteni — Thu, 13 Aug 2020 17:43:26 GMT

Hi edhans

Thank you! Unfortunately another problem is occurring right now, meaning that I'm not able anymore to build the relationship between my two sources (products lists and sales numbers). It remains inactive due to other relationships between sale and date.

Please see the screenshoot. I don't know how to solve this...

(Take everything you see here below as a try)

Re: Power Query - Bridge table - Weeknum, Date

edhans — Thu, 13 Aug 2020 18:04:49 GMT

I'm not 100% clear on your tables. I know what is causing this inactive relationship though.

DIM tables are dimension tables and should describe your data. So a date table (describes dates), customer table (describes customers), product table (describes products.)

Your FACT tables are facts, or transactions, so sales, purchase, orders, etc.

FACT tables should not be related to each other, and DIM tables should not be related to each other.

You've related what looks to me like your DIM Prodcut Table to your DIM Date table. Why are you doing that? In other words, what date relationship do you care about that is between the dates in the date table and your product table?

I also don't know what your item table and product tables are. That sounds like the same thing to me. Can those be merged into a single table? I know I know almost nothing about your data, but to me, this should be 3 tables:
DIM Products
DIM Date
FACT Sales

The DIMs should be related 1 to many to the Sales table.

Post back with more info. You are getting deep into data modeling here, which is how you ultimately build a successful DAX model!

Re: Power Query - Bridge table - Weeknum, Date

LorenzoMolteni — Thu, 13 Aug 2020 20:29:37 GMT

Hi edhans

Thanks for your reply!

First of all, no need for the Items tabe. That was just a try I was giving to solve the issue.

As you just said, there should be 3 tables in Power Pivot:

- Products list: This is a selection of products that gets uploaded and goes on sale. I have a new selection/list once a week (uploading on Sunday). I get this from Power Query (import from folder). This contains the Product No., Brand, Pre-shopping date (date of upload), Week and now the Fake Date you suggested to create. I want to study the sales on these products on their first week only being live (i.e. if I filter for CW16 I only want to see sale performence for the products I uploaded in CW16 during their first week - not taking into account sales data from CW17 on)

- Sale: This is basically the daily order report for all products we sell. I also get this from Power Query (import from folder). We have here different data, but the ones we need to definetely have are Product No., QTY, Revenue, Date (sale date).

Date: This is simply the date table you suggested to create with Date and Year/Week.

Given the above, I assume I have to create a connection between:

- Product List & Sales on Product No.

- Date & Sales on Date

- Product List & Date on Fake Date / Date

I may have misunderstood your first advices, but not sure of that. How shall I overcome the issue?

Please find attached how the final report looks like. My report occurs when filtering for more/different weeks.

Thanks so much!

Lorenzo

Re: Power Query - Bridge table - Weeknum, Date

edhans — Thu, 13 Aug 2020 20:59:54 GMT

Why not create a true/false field in the product table that determines if this is the first week? Date.IsInCurrentWeek() will do it based on today (refresh date) but you can tweak that or calculate your own week number in there. I don't think there is a need to relate that to the date table specifially. Then just add a filter in your measures for this field.

New Sales = CALCULATE( SUM(Sales), FILTER( Sales, RELATED(Product[IsCurrentWeek]) = TRUE() ) )

I typed that in free hand. Didn't test it at all, but you get the idea of what I am considering here.

Re: Power Query - Bridge table - Weeknum, Date

LorenzoMolteni — Fri, 14 Aug 2020 07:29:01 GMT

Hi edhans

How would this help? The date we have in the Products table is always in the first week because that's the upload date.

My concerns are on the connection between the Products Table and the Sales Table. I don't know how to manage the data so that the pivot, when filtering by week, only takes the first week sales data from the Sales Table.

Let's say this what I have currently from Power Query

Procuts Lists

Week	Product No.	Brand	Category	Upload date
30	P001	...	...	26.07.2020
31	P002			02.08.2020
31	P003			02.08.2020
31	P004			02.08.2020
32	P005			09.08.2020
32	P006			09.08.2020

Sales

Product No.	QTY	Revenue	Sale Date
P001	1	100	26.07.2020
P001	2	200	27.07.2020
P001	4	400	03.08.2020
P003	2	300	10.08.2020
P006	1	50	10.08.2020

So, in this case for example, in my pivot table if I filter now by CW30 I'll should be seeing the following

Product No.	QTY	Revenue
P001	3	300

And not

Product No.	QTY	Revenue
P001	7	700

Again, if I filter by CW32 I should be seeing

Product No.	QTY	Revenue
P006	1	50

Is this clear for you? Sorry but I really can't find a solution for this...

Thanks a lot!

Lorenzo

Re: Power Query - Bridge table - Weeknum, Date

Anonymous — Sun, 16 Aug 2020 19:56:07 GMT

let me know if what follows is close to your goal:

query Product:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZQ0lEKMDAwBFKJiUAiGcQyMtM3MNc3MjAyUIrVASoyhCgyAlJJSSBFIJaBkb6BBaYiYxSTcCgyAckn41JkBFFkCrcOZKaBJVZFZnDr0BXFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Week = _t, Product = _t, Brand = _t, Category = _t, #"Upload date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"Product", type text}, {"Brand", type text}, {"Category", type text}, {"Upload date", type date}},"it-IT"), #"Added Custom" = Table.AddColumn(#"Changed Type", "Qty", each List.Sum(Table.SelectRows(sales, (r)=> (r[wk]=[Week] and r[Product]=[Product] ))[QTY])), #"Added Custom 1" = Table.AddColumn(#"Added Custom", "rev", each List.Sum(Table.SelectRows(sales, (r)=> (r[wk]=[Week] and r[Product]=[Product] ))[Revenue])) in #"Added Custom 1"

query sales:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwMFTSUQJjAwMgaWSmb2Cub2RgZKAUqwOXNwJhiLw5NnkTEAbLGxjrG1igyBtD9RuD5Q0N0OXNoPabokvHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, QTY = _t, Revenue = _t, #"Sale Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{"Sale Date", type date}), #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type",{{"QTY", Int64.Type}, {"Revenue", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type2", "wk", each Date.WeekOfYear([Sale Date],2)), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"wk", Int64.Type}}) in #"Changed Type1"

with the added auxiliar column wk

The wk column was "forcibly" obtained from the "sale dates" column in order to have the same result for 7/26 and 7/27, which could be two days of different weeks, according to other conventions.

e questo è il risultato:

Re: Power Query - Bridge table - Weeknum, Date

LorenzoMolteni — Fri, 04 Sep 2020 14:08:48 GMT

Hi Anonymous

Thanks for your support and sorry for my late reply.

I think this may actually work, but do you have any idea why it's extremely slow to upload and refresh? Despite the fact I don't have a huge amount data to study, it's still taking hours to refresh every time.

Thanks a lot!

Lorenzo

Re: Power Query - Bridge table - Weeknum, Date

Anonymous — Fri, 04 Sep 2020 14:29:40 GMT

I'm sorry, but I no longer remember the details of this problem.
Since seems that there is a performance problem, to try to find a useful solution you should also provide the dimensional data or better the tables (even with fake data, but with the same structure as your real data) on which to reason to try any changes to make the algorithm more efficient.

Re: Power Query - Bridge table - Weeknum, Date

LorenzoMolteni — Fri, 04 Sep 2020 15:18:26 GMT

Hi Anonymous

As explained above, here the two sources I have and the relative queries

Proudcts List (1x week)

No.	Brand Description	Description	Main Waregroup Description	Pre Shopping Date
P00472852	Paco Rabanne	shoulder bags	bags	05.07.2020
P00478613	Zimmermann	jumpsuits	clothing	05.07.2020
P00478618	Zimmermann	dresses	clothing	05.07.2020
P00481172	Bottega Veneta	pumps	shoes	05.07.2020
P00481332	Bottega Veneta	high sandals	shoes	05.07.2020
P00482376	Dries van Noten	high sandals	shoes	05.07.2020
P00482532	Bottega Veneta	sunglasses	accessoires	05.07.2020
P00483132	Bottega Veneta	clutches	bags	05.07.2020
P00483185	Bottega Veneta	shoulder bags	bags	05.07.2020
P00483230	Off-White	totes	bags	05.07.2020
P00483529	Saint Laurent	belts	accessoires	05.07.2020
P00483897	Off-White	skirts	clothing	05.07.2020
P00483908	Off-White	jackets	clothing	05.07.2020
P00484251	Balmain	tops	clothing	05.07.2020
P00484294	Balmain	skirts	clothing	05.07.2020
P00487097	Alexandre Vauthier	skirts	clothing	05.07.2020
P00487691	Loewe	pumps	shoes	05.07.2020
P00487799	Bottega Veneta	pants	clothing	05.07.2020
P00487823	Bottega Veneta	dresses	clothing	05.07.2020
P00487955	Gucci	hats/hair accessories	accessoires	05.07.2020
P00487956	Gucci	hats/hair accessories	accessoires	05.07.2020
P00487957	Gucci	hats/hair accessories	accessoires	05.07.2020
P00488001	Gucci	shoulder bags	bags	05.07.2020
P00488352	Valentino	tops	clothing	05.07.2020
P00488545	Loewe	jackets	clothing	05.07.2020
P00488559	Tom Ford	pumps	shoes	05.07.2020
P00488571	Tom Ford	high sandals	shoes	05.07.2020
P00488591	Loewe	jackets	clothing	05.07.2020
P00488670	Loewe	tops	clothing	05.07.2020
P00488753	Valentino	pants	clothing	05.07.2020
P00489313	Givenchy	pumps	shoes	05.07.2020
P00489827	Saint Laurent	shorts	clothing	05.07.2020
P00489853	Saint Laurent	capes/ponchos	clothing	05.07.2020
P00489868	Saint Laurent	dresses	clothing	05.07.2020
P00490811	Stella McCartney	ankle boots	shoes	05.07.2020
P00491849	Fendi	dresses	clothing	05.07.2020
P00496045	Givenchy	dresses	clothing	05.07.2020
P00496717	Gucci	tops	clothing	05.07.2020
P00496786	Dolce & Gabbana	dresses	clothing	05.07.2020
P00496787	Dolce & Gabbana	dresses	clothing	05.07.2020
P00497283	Rick Owens	dresses	clothing	05.07.2020
P00497853	Balmain	swimwear	clothing	05.07.2020
P00498363	Gucci	sweatshirts	clothing	05.07.2020
P00499108	Costarellos	cocktail/gowns	clothing	05.07.2020
P00499296	Chloé	dresses	clothing	05.07.2020
P00499632	The Attico	dresses	clothing	05.07.2020
P00499638	The Attico	pants	clothing	05.07.2020
P00499715	Paco Rabanne	dresses	clothing	05.07.2020
P00491734	Fendi	jackets	clothing	05.07.2020
P00499746	Paco Rabanne	casual tops	clothing	05.07.2020

let Source = Folder.Files("T:\Public\MERCHANDISING RETAIL\Pre-Shopping\Pre-Shopping Lists"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Pre-Shopping Lists", each #"Transform File from Pre-Shopping Lists"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Pre-Shopping Lists"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Pre-Shopping Lists", Table.ColumnNames(#"Transform File from Pre-Shopping Lists"(#"Sample File"))), #"Added Custom" = Table.AddColumn(#"Expanded Table Column1", "Week", each Date.WeekOfYear([Pre Shopping Date])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Source.Name"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Week", "No.", "Brand Description", "Description", "Main Waregroup Description"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"No.", "No"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Pre Shopping Date", type date}, {"Week", Int64.Type}, {"No", type text}, {"Brand Description", type text}, {"Description", type text}, {"Main Waregroup Description", type text}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Qty", each List.Sum(Table.SelectRows(DOR, (r)=> (r[Week]=[Week] and r[No]=[No] ))[QTY])), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Revenue", each List.Sum(Table.SelectRows(DOR, (r)=> (r[Week]=[Week] and r[No]=[No] ))[Revenue Net])) in #"Added Custom2"

Daily Order Report (1x Day) avg. 5.000 rows

Description

Style

Item Brand

Main Waregroup

Department Group

Onlineset

For Sale Date

Season

Is Core Item

Is Exclusive

Colour

QTY

sale price

Revenue Net

Qty Discount

Revenue Discount

P00394784 belts

Unassigned

Off-White

accessories

WOMENSWEAR

2000_03_10

8/8/2019

20/1

6010 - YELLOW BLACK

2.340 €

P00435551 sneakers

Unassigned

Stella McCartney

shoes

WOMENSWEAR

11/29/2019

20/1

9099 - WH/MULTI

175

2.704 €

P00440255 hats/hair accessories

Unassigned

Jacquemus

accessories

WOMENSWEAR

2020_03_16

3/13/2020

20/1

NATURAL - NATURAL

227

2.019 €

P00445233 shoulder bags

Unassigned

Loewe

bags

MENSWEAR

2020_06_01

1/15/2020

20/1

2530 - BROWN

175

1.507 €

P00422869 belts

Unassigned

Off-White

accessories

WOMENSWEAR

12/9/2019

20/1

Yes

0110 - WHITE BASE/BLACK WRITING

107

792 €

P00494290 espadrilles

Unassigned

Castañer

shoes

WOMENSWEAR

2020_06_09

6/8/2020

20/2

100 - NEGRO

105

888 €

P00394419 totes

Unassigned

Off-White

bags

WOMENSWEAR

2000_03_10

5/29/2019

20/1

1001 - BLACK WHITE

234

1.564 €

P00452928 sneakers

Unassigned

Acne Studios

shoes

MENSWEAR

2020_06_10

3/17/2020

20/1

IVORY WHIT - IVORY WHITE

156

902 €

P00450812 shoulder bags

Unassigned

Jil Sander

bags

WOMENSWEAR

2020_02_28

4/1/2020

20/1

680 - CANDY

330

1.802 €

let Source = Folder.Files("\\mytheresa.local\Files\Public\MERCHANDISING RETAIL\RECYCLING\KW\DOR"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from DOR", each #"Transform File from DOR"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from DOR"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from DOR", Table.ColumnNames(#"Transform File from DOR"(#"Sample File (2)"))), #"Added Custom" = Table.AddColumn(#"Expanded Table Column1", "Date", each Text.Range([Source.Name],0,8)), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name", "Style", "Item Brand", "Main Waregroup", "Department Group", "Onlineset", "For Sale Date", "Season", "Is Core Item", "Is Exclusive", "Colour", "sale price", "Qty Discount", "Revenue Discount"}) in #"Removed Columns"

Thank you!

Lorenzo

Re: Power Query - Bridge table - Weeknum, Date

Anonymous — Fri, 04 Sep 2020 16:09:56 GMT

peraphs a merge could be more performant:

let Source = Table.NestedJoin(productm, {"Product", "Week"}, sales, {"Product", "wk"}, "product.1", JoinKind.FullOuter), #"Aggregated product.1" = Table.AggregateTableColumn(Source, "product.1", {{"QTY", List.Sum, "Sum of product.1.QTY"}, {"Revenue", List.Sum, "Sum of product.1.Revenue"}}) in #"Aggregated product.1"

this is the same sales table

Re: Power Query - Bridge table - Weeknum, Date

ImkeF — Fri, 04 Sep 2020 17:30:18 GMT

Hi Anonymous ,

if my understanding is correct, you can significantly speed up your first query using this method: https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-powerquery-powerbi/

With regards to your second suggestion, I'd expect it to be very slow as well. Here I've described how to speed it up: https://www.thebiccountant.com/2019/10/28/performance-tip-for-aggregations-after-joins-in-power-query-and-power-bi/

Re: Power Query - Bridge table - Weeknum, Date

Anonymous — Fri, 04 Sep 2020 19:59:42 GMT

<<... think this may actually work, but do you have any idea why it's extremely slow to upload and refresh? Despite the fact I don't have a huge amount data to study, it's still taking hours to refresh every time. ..>>

I tried with the data you provided, making them "proliferate" as you do with lactic ferments to make yogurt, obtaining a sales table of more than 5000 rows and a table of products of 50 rows.
using the old algorithm it took a few minutes (perhaps less than a minute, measured by "hand").
So I have no idea if your size is why it takes so long on your pc.
In fact the code is not meant to be efficient. I just tried to be clear.
Therefore, for example, I repeated for two you want the same crossing of comparisons between the two tables (sales and product). once for the quantities and a second for the revenue.
This means 5000X50 = 250000 theoretical comparisons repeated twice. So 500,000 comparisons, which in fact are not many.

A first cut in time could be obtained by combining these two operations into one (therefore, theoretically, halving the comparisons from 500K to 250K)

with this ugly expression:

# "Added Custom" = Table.AggregateTableColumn (Table.AddColumn (# "Changed Type", "QR", each Table.SelectRows (sales, (r) => (r [wk] = [Week] and r [Product] = [Product])) [[QTY], [Revenue]]), "QR", {{"QTY", List.Sum, "sumQ"}, {"Revenue", List.Sum, "sumR"}} )

Another way, which I have used on other occasions, is to do a groupby of the tables by product and week and then limit the searches to subgroups.
to give an example, if I have to search by crossing two tables of 1000 rows each I need 1M comparisons.

If I can divide the two tables into 10 pieces of 100 that correspond to each other, I have to make 10X100X100 = 100k comparisons, therefore a tenth compared to before.

all that said, you could give some more details on the size of your tables.

It is not clear to me how many weeks and therefore how many rows the product table has.

Re: Power Query - Bridge table - Weeknum, Date

Anonymous — Sat, 05 Sep 2020 11:01:02 GMT

a different way, grouping the sales table

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwMFTSUQJjAwMgaWSmb2Cub2RgZKAUqwOXNwJhiLw5NnkTEAbLGxjrG1igyBtD9RuD5Q0N0OXNoPabokvHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, QTY = _t, Revenue = _t, #"Sale Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sale Date", type date},{"QTY", Int64.Type}, {"Revenue", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "wk", each Date.WeekOfYear([Sale Date],2), type number), #"Grouped Rows" = Table.Group(#"Added Custom", {"Product","wk"}, {{"all", each _, type table [Product=text, QTY=nullable number, Revenue=nullable number, Sale Date=nullable date]}}) in #"Grouped Rows"

then doing research on subgroups should be faster

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc1BDoAgDATAv/RMwlIQ9RfeCQfg/3+wDUqU6KXbpJNtSuRBhg7ASZQio+nG0WK1DAZlI8h1xBK1KtINbLE9EHfkX01uQldT0Hu70U/TMt5pJ/ZPFMe7GeUT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t, Product = _t, Brand = _t, Category = _t, #"Upload date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"Product", type text}, {"Brand", type text}, {"Category", type text}, {"Upload date", type date}},"it-IT"), ac1= Table.ExpandListColumn(Table.AddColumn( #"Changed Type", "QR", each List.Select(Table.SelectRows(salesGrp, (r)=> (r[Product]=[Product]))[all], (l)=>l[wk]{0}=[Week] )),"QR"), tac= Table.AggregateTableColumn(ac1, "QR",{{"QTY",List.Sum,"sumQ"},{"Revenue",List.Sum,"sumR"}}) in tac

but to test and possibly improve the speed of execution we need the details (only the structur and sizes, of course)of the real dataset you are working on

Re: Power Query - Bridge table - Weeknum, Date

LorenzoMolteni — Tue, 15 Sep 2020 08:33:58 GMT

Hi Anonymous

Thanks for the support!
Unfortunately none or the above suggestions is drastically speeding my performance up.

As I said above, I have 1 Product List file adding every week (only 50 items/rows a week max) and 1 Sales file adding every single day - 5 days a week - (around 300 rows every single day). This is the size of my data.

If for example, I take into consideration one month, I may have 200 rows for the Products Lists and around 7.000 for sales.

Is this kinda of clear for you now?

Thank you!