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.
Hello everyone!
I am struggling with this thing over here. I did my research but couldn't manage to find the solution or I could not make it work by following other similar solutions.
Here is what I am trying to do on Power Query:
(Table A) I have a list that recorded item name with price changed date and the price. (it only has a price changed date)
(Table B) I havce a list of sold items wih date and qantity. (But I have excluded the QTY part here)
I want to look up (vlookup) changed prices prices (from table A) to sold items table (to table B). I want all dates with each product. I want the price to fill up or get repeated on every day until the next price change
This is table A
Price changed date | Product name | Item price change |
5/15/2020 | Ju juice | 100 |
5/18/2020 | Ju juice | 150 |
5/22/2020 | Ju juice | 120 |
5/15/2020 | Yo Yoghurt | 10 |
5/20/2020 | Yo Yoghurt | 13 |
5/23/2020 | Yo Yoghurt | 15 |
5/15/2020 | Br bread | 1000 |
5/19/2020 | Br bread | 2000 |
5/22/2020 | Br bread | 3000 |
This is Table B
Date |
5/15/2020 |
5/16/2020 |
5/17/2020 |
5/18/2020 |
5/19/2020 |
5/20/2020 |
5/21/2020 |
5/22/2020 |
5/23/2020 |
5/24/2020 |
5/25/2020 |
5/26/2020 |
and this is the result or output I want:
Date | Product name | item price change |
5/15/2020 | Ju juice | 100 |
5/16/2020 | Ju juice | 100 |
5/17/2020 | Ju juice | 100 |
5/18/2020 | Ju juice | 150 |
5/19/2020 | Ju juice | 150 |
5/20/2020 | Ju juice | 150 |
5/21/2020 | Ju juice | 150 |
5/22/2020 | Ju juice | 120 |
5/23/2020 | Ju juice | 120 |
5/24/2020 | Ju juice | 120 |
5/25/2020 | Ju juice | 120 |
5/15/2020 | Yo Yoghurt | 10 |
5/16/2020 | Yo Yoghurt | 10 |
5/17/2020 | Yo Yoghurt | 10 |
5/18/2020 | Yo Yoghurt | 10 |
5/19/2020 | Yo Yoghurt | 10 |
5/20/2020 | Yo Yoghurt | 13 |
5/21/2020 | Yo Yoghurt | 13 |
5/22/2020 | Yo Yoghurt | 13 |
5/23/2020 | Yo Yoghurt | 15 |
5/24/2020 | Yo Yoghurt | 15 |
5/25/2020 | Yo Yoghurt | 15 |
5/15/2020 | Br bread | 1000 |
5/16/2020 | Br bread | 1000 |
5/17/2020 | Br bread | 1000 |
5/18/2020 | Br bread | 1000 |
5/19/2020 | Br bread | 2000 |
5/20/2020 | Br bread | 2000 |
5/21/2020 | Br bread | 2000 |
5/22/2020 | Br bread | 3000 |
5/23/2020 | Br bread | 3000 |
5/24/2020 | Br bread | 3000 |
5/25/2020 | Br bread | 3000 |
Hope I made self clear. Please let me know if I need to require any other information.
Thank you
Solved! Go to Solution.
If you can make a table with the distinct products (by editing Table A or you may have a dimension already), you can then add the TableProducts to TableB to generate a table with all dates from Table B and all products in the TableProducts
You do this by adding a custom column to Table B like this
Expand the column and sort it appropriately.
You can then Merge this table with Table A (left join) to bring in the Price change data.
Here's the M code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU3NNU3MjAyUIrVAfPMUHjmKDwLFJ4lMs/IAIVniMIzQuEZo/BMUHgobjGCuSUWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each TableProducts),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Product name"}, {"Custom.Product name"}),
#"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Custom.Product name", Order.Ascending}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"Custom.Product name", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Date", "Custom.Product name"}, TableA, {"Price changed date", "Product name"}, "TableA", JoinKind.LeftOuter),
#"Expanded TableA" = Table.ExpandTableColumn(#"Merged Queries", "TableA", {"Item price change"}, {"TableA.Item price change"}),
#"Sorted Rows1" = Table.Sort(#"Expanded TableA",{{"Custom.Product name", Order.Ascending}, {"Date", Order.Ascending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows1",{"TableA.Item price change"})
in
#"Filled Down"
The last 'Filled Down' step will work for your data but it's a slight cheat because there is a pricechange value for each product on the first date This may not be true for your real data. There are other ways to do this more precisely (add a DAX column)
Hi @Anonymous
Please check the answer by HotChilli.
If you have any problem, feel free to ask here.
Best Regards
Maggie
If you can make a table with the distinct products (by editing Table A or you may have a dimension already), you can then add the TableProducts to TableB to generate a table with all dates from Table B and all products in the TableProducts
You do this by adding a custom column to Table B like this
Expand the column and sort it appropriately.
You can then Merge this table with Table A (left join) to bring in the Price change data.
Here's the M code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU3NNU3MjAyUIrVAfPMUHjmKDwLFJ4lMs/IAIVniMIzQuEZo/BMUHgobjGCuSUWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each TableProducts),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Product name"}, {"Custom.Product name"}),
#"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Custom.Product name", Order.Ascending}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"Custom.Product name", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Date", "Custom.Product name"}, TableA, {"Price changed date", "Product name"}, "TableA", JoinKind.LeftOuter),
#"Expanded TableA" = Table.ExpandTableColumn(#"Merged Queries", "TableA", {"Item price change"}, {"TableA.Item price change"}),
#"Sorted Rows1" = Table.Sort(#"Expanded TableA",{{"Custom.Product name", Order.Ascending}, {"Date", Order.Ascending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows1",{"TableA.Item price change"})
in
#"Filled Down"
The last 'Filled Down' step will work for your data but it's a slight cheat because there is a pricechange value for each product on the first date This may not be true for your real data. There are other ways to do this more precisely (add a DAX column)
Thank you very much for your eplanation with a screenshot and the M code. very detailed. I really appreciate it. @HotChilli
But I am having a problem doing this. I did everysteps until: Merged with item price change table and Expanded Item Price change column. but its getting duplicated. each product and each price are getting duplicated like 3 or 4 times.
What am I doing wrong?
DateCustom.TableProductsPriceChange.item Price change . (I deleted some results from the table below. maximum character limit)
5/15/2020 | Br bread | 1000 |
5/15/2020 | Br bread | 1000 |
5/15/2020 | Br bread | 1000 |
5/15/2020 | Br bread | 1000 |
5/15/2020 | Br bread | 2000 |
5/15/2020 | Br bread | 2000 |
5/15/2020 | Br bread | 2000 |
5/15/2020 | Br bread | 3000 |
5/15/2020 | Br bread | 3000 |
5/15/2020 | Br bread | 3000 |
5/15/2020 | Br bread | 3000 |
5/16/2020 | Br bread | 1000 |
5/16/2020 | Br bread | 1000 |
5/16/2020 | Br bread | 1000 |
5/16/2020 | Br bread | 1000 |
5/16/2020 | Br bread | 2000 |
5/16/2020 | Br bread | 2000 |
5/16/2020 | Br bread | 2000 |
5/16/2020 | Br bread | 3000 |
5/16/2020 | Br bread | 3000 |
5/16/2020 | Br bread | 3000 |
5/16/2020 | Br bread | 3000 |
5/17/2020 | Br bread | 1000 |
5/17/2020 | Br bread | 1000 |
5/17/2020 | Br bread | 1000 |
5/17/2020 | Br bread | 1000 |
5/17/2020 | Br bread | 2000 |
5/17/2020 | Br bread | 2000 |
5/17/2020 | Br bread | 2000 |
5/17/2020 | Br bread | 3000 |
5/17/2020 | Br bread | 3000 |
5/17/2020 | Br bread | 3000 |
5/17/2020 | Br bread | 3000 |
5/18/2020 | Br bread | 1000 |
5/18/2020 | Br bread | 1000 |
5/18/2020 | Br bread | 1000 |
5/18/2020 | Br bread | 1000 |
5/18/2020 | Br bread | 2000 |
5/18/2020 | Br bread | 2000 |
5/18/2020 | Br bread | 2000 |
5/18/2020 | Br bread | 3000 |
5/18/2020 | Br bread | 3000 |
5/18/2020 | Br bread | 3000 |
5/18/2020 | Br bread | 3000 |
5/19/2020 | Br bread | 1000 |
let
Source = Excel.CurrentWorkbook(){[Name="Date"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each TableProducts),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"TableProducts"}, {"Custom.TableProducts"}),
#"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Custom.TableProducts", Order.Ascending}}),
#"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Custom.TableProducts"}, TableA, {"Product name"}, "TableA", JoinKind.LeftOuter),
#"Expanded TableA" = Table.ExpandTableColumn(#"Merged Queries", "TableA", {"item Price change"}, {"TableA.item Price change"})
in
#"Expanded TableA"
Hi there,
I believe the issue is with your join statement:
"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Custom.TableProducts"}, TableA, {"Product name"}, "TableA", JoinKind.LeftOuter),
This reads that you joined the two tables by [Product Name] = [Custom.TableProducts]. You should have joined them by the date field (just edit it to match @HotChilli's example).
What happen is Table B found 3 or 4 different records for that product on each price date change and it is showing them all to you instead of that specific date.
I'm curious... let me know if that worked. I'm only a few weeks into Power BI, still trying to learn the ropes myself. It gets easier the more you do it.
@Anonymous
Not much difference. now its repeating everysingle difference price for everysingle day for each products
thank you for your reply
DateCustom.TableProductsPriceChange.item Price change
5/15/2020 | Br bread | 10 |
5/15/2020 | Br bread | 1000 |
5/15/2020 | Br bread | 100 |
5/16/2020 | Br bread | 100 |
5/16/2020 | Br bread | 1000 |
5/16/2020 | Br bread | 10 |
5/17/2020 | Br bread | 100 |
5/17/2020 | Br bread | 10 |
5/17/2020 | Br bread | 1000 |
5/18/2020 | Br bread | 10 |
5/18/2020 | Br bread | 150 |
5/18/2020 | Br bread | 1000 |
5/19/2020 | Br bread | 2000 |
5/19/2020 | Br bread | 10 |
5/19/2020 | Br bread | 150 |
5/20/2020 | Br bread | 150 |
5/20/2020 | Br bread | 2000 |
5/20/2020 | Br bread | 13 |
5/21/2020 | Br bread | 2000 |
5/21/2020 | Br bread | 150 |
5/21/2020 | Br bread | 13 |
5/22/2020 | Br bread | 3000 |
5/22/2020 | Br bread | 13 |
5/22/2020 | Br bread | 120 |
5/23/2020 | Br bread | 3000 |
5/23/2020 | Br bread | 15 |
5/23/2020 | Br bread | 120 |
5/24/2020 | Br bread | 3000 |
5/24/2020 | Br bread | 120 |
5/24/2020 | Br bread | 15 |
5/25/2020 | Br bread | 3000 |
5/25/2020 | Br bread | 120 |
5/25/2020 | Br bread | 15 |
5/26/2020 | Br bread | null |
Double check the original code or post your updated code. What your saying doesn't make sense. If you join Table A to Table B by the date field there should be no way the price is associated with a different date. You are quite literally filtering on that value. You may see multiple records for other reasons, but the price would only show up for that day.
Are you certain these are distinct values in Table A?
Umm... just thought of something, double check your merge calculation. I think you might have mixed up Table A and B. If you were to left join to Table B, starting with values from Table A you would get each price attached to all the dates.
@Anonymous
at first i merged the date with distinct item name (by add custom column). it was successful
then I merged it with the "price change" table (Table A). then it got mixed up
Code? It's a filter issue
the code is the code that i have posted above
The steps in a Power Query query are like stages in a recipe. Each one has to be right in order to proceed to the next.
It looks to me that this line is the first that goes wrong:
Table.ExpandTableColumn(#"Added Custom", "Custom", {"TableProducts"}, {"Custom.TableProducts"})
it should be
Table.ExpandTableColumn(#"Added Custom", "Custom", {"Product name"}, {"Custom.Product name"})
of course the names could be different at your side.
Usually when someone posts M code on the forum, the best way to proceed is to paste it into the 'advanced editor', replacing everything apart from the first Source step. You can then debug each step if there are errors (which should only be caused by different table/column names.
@Anonymous Your efforts are appreciated.
Thank you for your respons. I am sorry for keep bothering you. I really appreciate your effort @HotChilli
about the
@HotChilli wrote:The steps in a Power Query query are like stages in a recipe. Each one has to be right in order to proceed to the next.
It looks to me that this line is the first that goes wrong:
Table.ExpandTableColumn(#"Added Custom", "Custom", {"TableProducts"}, {"Custom.TableProducts"})
Table.ExpandTableColumn(#"Added Custom", "Custom", {"Product name"}, {"Custom.Product name"})
of course the names could be different at your side
On this. TableProducts is the new table I created with distinct product names (like table C). And the header name of that table is TableProducts too. the header is called same as the table name.
1. I added a custom column with product names on Date table.
2. and then I merged it with price change with left join. (the join primary key was by product names)
3. then I lost at filled down part because my step 2 gone unsuccessful.
Or should I share my pbx file?
Or os that expandTable should be from the Price change table?
@Anonymous Thank you too
Yes, please share the file
Orange part is the input part. and the green part is the desired output or result
I was hoping to see the pbix so it can be debugged.
@HotChilli I am sorry. here is the pbix file. Sorry for a late reply. We were having a difficulty with our internet service
OK,
First thing was that the Date table had 3 entries for each day so I removed duplicates. TableProducts table had one column also called TableProducts and this confused the 'Expand Column' step. (I notice the Date table also has a column of the same name, so I think you should be careful with this naming policy. Best to make them different)
The sorting of columns wasn't quite working due to the data type of 'Any', it needed to be text.
The file is attached.
I switched off 'Enable Load' from the Date query.
Good luck.
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 |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |