Power Query - Bridge table - Weeknum, Date

[LorenzoMolteni]

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

[LorenzoMolteni]

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