cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Re: vlookup Item price change date

the code is the code that i have posted above

Highlighted
Super User III
Super User III

Re: vlookup Item price change date

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.

 

@bflury    Your efforts are appreciated. 

Highlighted
Helper I
Helper I

Re: vlookup Item price change date

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? 

 

@bflury  Thank you too

Highlighted
Super User III
Super User III

Re: vlookup Item price change date

Yes, please share the file

Highlighted
Helper I
Helper I

Re: vlookup Item price change date

@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 

Highlighted
Super User III
Super User III

Re: vlookup Item price change date

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

Highlighted
Helper I
Helper I

Re: vlookup Item price change date

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

 

 

Highlighted
Super User III
Super User III

Re: vlookup Item price change date

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.

Highlighted
Helper I
Helper I

Re: vlookup Item price change date

@HotChilli  Thank you very much sir!  it is workig perfectly! 🙏

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors