• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Check that a price list has all the prices it should have.

fresope

New Member
We have Microsoft business central as our ERP system and I have conected an Excel file to the price list table of BC to check that we have all the prices that should be there

I have a pricelist with 1000 items. (Actually it's more items and more prices, in multiple companies but I simplify for the example here)
All of these 1000 items should have 10 different prices.
Price A
Price B
Price C
Price D
and so on.

Today, some of these itmes are missing certain prices.
In the Excel file you can see what I mean.
I need to add each price type to each item code so I can see what prices are missing.

I think it is clear if you check the file.
 

Attachments

  • price list example.xlsx
    11.6 KB · Views: 8
Achieved with Power Query and Full Outer Join

Code:
let
    T1 = Excel.CurrentWorkbook(){[Name="Tabell1"]}[Content],
    CT = Table.TransformColumnTypes(T1,{{"Item code", type text}, {"Name of price", type text}, {"Price", type number}}),
    T2=Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(CT, {"Item code","Name of price"},T2, {"Item code","Name of price"}, "NewTable", JoinKind.FullOuter),
    ET = Table.ExpandTableColumn(MQ, "NewTable", {"Item code", "Name of price"}, {"Item code.1", "Name of price.1"})


in
    ET
 

Attachments

  • price list example.xlsx
    20 KB · Views: 4
Hello @AlanSidman

Thank you for this. I think, when I see you answer, that I asked my question the wrong way. I did't leave complete information.

What I have is Table 1.
I have no table like table 2 (in the original file), all I have is a list with the name of the different price groups.
I have added this to the file.

The end result I want, is spot on what you have created

So the question really is, how I can create a table with all my item codes and for each item code I have 10 rows, one for each price type?

Is this a better explanation?
 

Attachments

  • price list example.xlsx
    20.7 KB · Views: 6
This is exactly it. Thank you very very much.
And the solution is so simple when I look at your code. You're good!

Very grateful!
 
Back
Top