Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have two tables, one with a list of quotations and another with the price update of those products.
I want to compare the price from the quotations with the price updates, the problem is that for every update, all products have their prices updated (many-to-many).
I tried to stablish a period with an initial datetime (the datetime of the update) and a last datetime (the datetime of the next update minus one minute), and with the IF condition, check if the quotation occurred between those datetimes.
The problem is, I cant ref those columns into the IF condition on the first table, the Power BI dont even show those columns to choose, I believe it is because I dont have any relationship between those tables (the relationship would be this same conditional).
I'm using the datetime format because these proccess occur in small periods of time (a few minutes), been necessary to know both date and time of each information.
Could you folks give me a help?
@marcospaulo yes you are not seeing the columns becuase there is no relationship,you need a product table which can be used to set relationship between those two tables and can work from there.
if you share sample data it will help to provide the solution
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
The first table (quotation) is like this:
Send Date | Time | Operation | Company | Product | Location | Period | Volume | Price | Status |
01/02/2018 | 15:00 | Buy | P | CONV | SE/CO | jan/18 | 2,96 | -R$ 12,90 | Lost |
01/02/2018 | 15:30 | Buy | D | CONV | SE/CO | jan/18 | 8,00 | -R$ 9,00 | Won |
01/02/2018 | 15:30 | Buy | H | CONV | SE/CO | jan/18 | 2,05 | -R$ 11,90 | Lost |
And the second (pricing) is like this:
Period | Date | Time | Operation | Location | Product | Price |
jan/18 | 01/02/2018 | 14:10:00 | Buy | SE/CO | CONV | -R$ 11,90 |
jan/18 | 01/02/2018 | 14:10:00 | Sell | SE/CO | CONV | -R$ 9,60 |
jan/18 | 01/02/2018 | 14:10:00 | Buy | N | CONV | -R$ 8,90 |
jan/18 | 01/02/2018 | 14:10:00 | Buy | NE | CONV | -R$ 11,90 |
jan/18 | 01/02/2018 | 14:10:00 | Buy | S | CONV | -R$ 11,90 |
jan/18 | 01/02/2018 | 14:10:00 | Sell | N | CONV | -R$ 6,60 |
jan/18 | 01/02/2018 | 14:10:00 | Sell | NE | CONV | -R$ 9,60 |
jan/18 | 01/02/2018 | 14:10:00 | Sell | S | CONV | -R$ 9,60 |
jan/18 | 01/02/2018 | 15:15:00 | Sell | SE/CO | CONV | -R$ 9,60 |
jan/18 | 01/02/2018 | 15:15:00 | Buy | N | CONV | -R$ 8,90 |
jan/18 | 01/02/2018 | 15:15:00 | Buy | NE | CONV | -R$ 11,90 |
jan/18 | 01/02/2018 | 15:15:00 | Buy | S | CONV | -R$ 11,90 |
jan/18 | 01/02/2018 | 15:15:00 | Buy | SE/CO | CONV | -R$ 11,90 |
jan/18 | 01/02/2018 | 15:15:00 | Sell | N | CONV | -R$ 6,60 |
jan/18 | 01/02/2018 | 15:15:00 | Sell | NE | CONV | -R$ 9,60 |
jan/18 | 01/02/2018 | 15:15:00 | Sell | S | CONV | -R$ 9,60 |
In your scenario, what's the logic to compare the price from the quotations with the price updates. For example: let's say the price of the first row in table quotation is -R$ 12,90. Then which one or ones should be compared in table pricing? To compare data between tables, we should get the relation between them first. Even though they are many to many.
Then with many to many relationship in Power BI. Generally, we can combine those columns into one Key column. Then use this key column to make one to one or one to many relationship. Something like:
Key = quotation[Product]&"-""ation[Company]&"-""ation[Period]
Thanks,
Xi Jin.
Hi @marcospaulo,
I think you can append those tables and work as one single table.
If I can help you let me know.
Thanks.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |