Solved: Re: vlookup Item price change date

Anonymous · ‎05-28-2020

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

HotChilli · ‎05-28-2020

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)

View solution in original post

v-juanli-msft · ‎05-29-2020

Hi @Anonymous

Please check the answer by HotChilli.

If you have any problem, feel free to ask here.

Best Regards

Maggie

HotChilli · ‎05-28-2020

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)

Anonymous · ‎06-01-2020

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"

Anonymous · ‎06-01-2020

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 · ‎06-01-2020

@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

Anonymous · ‎06-01-2020

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?

Anonymous · ‎06-01-2020

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 · ‎06-01-2020

@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

Anonymous · ‎06-01-2020

Code? It's a filter issue

Anonymous · ‎06-02-2020

the code is the code that i have posted above

HotChilli · ‎06-02-2020

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.

Anonymous · ‎06-02-2020

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

HotChilli · ‎06-03-2020

Yes, please share the file

Anonymous · ‎06-03-2020

@HotChilli

Orange part is the input part. and the green part is the desired output or result

This is the "OneDrive" link to the file

HotChilli · ‎06-04-2020

I was hoping to see the pbix so it can be debugged.

Anonymous · ‎06-08-2020

@HotChilli I am sorry. here is the pbix file. Sorry for a late reply. We were having a difficulty with our internet service

HotChilli · ‎06-09-2020

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.