merge queries with dividing with a data on another query

[selpaqm]

Hi,

I have sales figures on one excel but in different sheets like GBP, EUR and USD sheets and all sheets are on same headers. as below 

Week

Customer

QTY

sales

On weekly basis, I am collating data on sharepoint folder. 

and also I have another table for currencies.

Week	GBP	USD	EUR
1	1	1.15	1.33
2	1	1.16	1.35
3	1	1.15	1.34

So I want to create one query like;

GBP Sheet

Week	Customer	QTY	sales
1	aaa	100	500
2	bbb	300	1500

EUR Sheet

Week	Customer	QTY	sales
1	ccc	50	100
2	ddd	100	200

USD sheet

Week	Customer	QTY	sales
1	eee	500	1000
2	fff	1000	2000

Result

Week	Customer	QTY	sales
1	aaa	100	500/1
1	ccc	50	100/1.15
1	eee	500	1000/1.33
2	bbb	300	1500/1
2	ddd	100	200/1.16
2	fff	1000	2000/1.35

[v-rongtiep-msft · Accepted solution]

Hi @selpaqm , 

Please refer to my pbix file to see if it helps you.

First, insert a custom in USD sheet,GBP sheet and EUR sheet like the following.

Then append the three sheet as new.

Then create two columns.

Column = MONRY[Week]&"-"&MONRY[Attribute]

Column = Append1[Week]&"-"&Append1[Custom]

Finally ,create a measure.

Measure =
VAR _result =
    CALCULATE (
        MIN ( MONRY[Value] ),
        FILTER ( ALL ( MONRY ), MONRY[Column] = SELECTEDVALUE ( Append1[Column] ) )
    )
RETURN
    MAX ( Append1[sales] ) & "/" & _result

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@selpaqm , I each sheet add sheet name(Curr Name) as column and create a combined column with [Week] & "-" [Curr name]

Unpivot the first table and create a new column there too [Week] & "-" [Curr name]

Merge these tables now and get the rate, you can now create new column to use rate abe value

https://radacad.com/pivot-and-unpivot-with-power-bi
Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query