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

help! Powerquery - identify one item of a order and then return value for all lines on order?

Hi i have data with many rows and orders

 

an order number can be used to have multiple products so there will be multiple rows of a duplicated sales order number

 

i want to be able to filter orders that contain a certain product from a certain manufacturer to further analyse those orders and their full order value.

 

i added a custom column so that i can identify orders that have a manufacturer of x and a level 3 hierarchy of y see below:

 

if[Manufacturer] = "Yazoo" and [Hierarchy Level 3]= "CONF" then "1" else "0"

 

However i need to get the value of everything on the order and not just the items flagged 1 or 0.

 

Can anyone help?

 

Example data:

 

Sum of Final Amount (£)Revenue GroupHierarchy Level 1Hierarchy Level 2Hierarchy Level 3PeriodManufacturerEntity CodeEntry NumberEntry TypeYear EndingPart NumberDescriptionInvoice NumberAmountFinal AmountCurrency CodeExchange RateAmount (£)Adjusted Amount (£)Customer NumberProduct TypeSales Order NumberPre InvoiceCreated DateIs Internal TransferConfiguration
£5ServicesMetalCONFIGURATIONCONFIGURATION1YAZOOUK15298602Item2017AHHHHIP FLASKPSINV017900GBP10023487SERVICESSOR1234001/02/17 09:43No1
£10ServicesPetCONFIGURATIONCONFIGURATION1TENUK15298603Item2017AKKKDOG TAGPSINV0100GBP10023487SERVICESSOR1234001/02/17 09:43No0

 

2 ACCEPTED SOLUTIONS

Hi @ballist1x,

 

I have been looking at your question and looking at the setup and with making some "ugly" coding you can do the following:

 

  • Create a new table with the following code:
Orders_Config_Summary =
FILTER (
    SUMMARIZE (
        ALL ( Table1[MANUFACTURER]; Table1[Hierarchy Level 3]; Table1[SALES_ORDER_NO] );
        Table1[SALES_ORDER_NO];
        Table1[MANUFACTURER];
        [Hierarchy Level 3]
    );
    Table1[MANUFACTURER] = "YAZOO"
        && Table1[Hierarchy Level 3] = "CONFIG"
)
  • Create a calculated column on the Data table with the following code:
Config_Data =
IF (
    LOOKUPVALUE (
        Orders_Config_Summary[SALES_ORDER_NO];
        Orders_Config_Summary[SALES_ORDER_NO]; Table1[SALES_ORDER_NO]
    )
        = BLANK ();
    "NOCONFIG";
    Table1[SALES_ORDER_NO]
)
  • Add a slicer with the config_data column to your report
    • Having no selection will give you the unfiltered data having a selection will get the orders with the config in it

 

Again this is very "ugly" coding and it's hard coded in the summary table the manufacturer and Hierarchy level, don't know if you need to have any changes of this to another manufacturer or hierarchy configuration but it's working with the information you gave.

 

Slicers_.gif

 

See attach  the pbix file (we tranfers only available for 7 days) in page 2 is this solution,

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Hi @ballist1x,

 

Add the following calculated column:

 

SORD OK =
IF (
    ISBLANK (
        LOOKUPVALUE (
            Orders_Config_Summary[SALES_ORDER_NO];
            Orders_Config_Summary[SALES_ORDER_NO]; Table1[SALES_ORDER_NO]
        )
    );
    "NO";
    "YES"
)

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

13 REPLIES 13
Super User III
Super User III

Hi @ballist1x,

 

 

Can you please clarify better.

 

It's seem to mee that you don't need to make a calculated column but create a table with the manufacturer and the hierarchy levels, however for me your question is not tottally clear.

 

Can you have some additional details / expected results.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Hi @ 

 

Sure that is a possibility.

 

 

i want to be able to get all of the data for orders with config lines added to them, and exclude all orders with no config in them, to work out which do and which do not.

 

i want to be able to calculate the final amount £ and see all of the details of row of an order but only for orders with config.

 

Regards

Hi @ballist1x,

 

sorry for asking but can you give some sample data and expected result.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Hi @MFelix the data sample has been pasted into the original post.

 

the expected result is that i can return all the rowds that have a shared SORD number with an order that has [Manufacturer] = "Yazoo" and [Hierarchy Level 3]= "CONF" then "1" else "0"

 

there are many SORDS and many lines within each order.

 

not every order has a line with CONFIG as the Hierarchy level 3.

 

i need to get all the lines group together that share an SORD with an Entry number that contains Hierarchy Level 3 CONFIG

Hi @ballist1x,

 

The sample is very reduced so to get the result you want it's difficult taking into account all the constrainments you identify on your post.

 

Just want to help you in the best way I can and with just two lines of information as you can imagine it's dificcult to get the final result.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Hi No problem, see below:

 

ENTITY_CODESOURCE_ENTRY_NOSOURCE_ENTRY_TYPEYEAR_ENDINGPERIODPOSTING_DATENAV_NO_CODENAV_NO_DESCRIPTIONDOCUMENT_NOQUANTITYSellCostmarginSOURCE_NOCUSTOMER_NAMETYPE_OF_SUPPLY_CODEMANUFACTURERSALES_ORDER_NOPRE_INVOICECREATED_DATEMODIFIED_DATERevenue GroupHierarchy Level 1Hierarchy Level 2Hierarchy Level 3
SG54733Item2018101/01/2018NXGPET1SG11111201010SH1123DOG BZITTINNEDSORD1001/02/2018 03:03 FOODFOODFOODFOOD
SG54734Item2018101/01/2018NXGPET3SG11136201010SH1125CATZITTUNASORD2001/02/2018 03:03 FOODFOODFOODFOOD
SG54735Item2018101/01/2018NXGPET5SG1115241055SH1127CATZITYAZOOSORD3001/02/2018 03:03 FOODFOODCONFIGCONFIG
SG54736Item2018101/01/2018NXGPET8SG11186201010SH1130DOG BZITTINNEDSORD3001/02/2018 03:03 FOODFOODFOODFOOD
SG54737Item2018101/01/2018NXGPET9SG111931055SH1131BINNYITYAZOOSORD4001/02/2018 03:03 FOODFOODCONFIGCONFIG
SG54738Item2018101/01/2018NXGPET12SG11223201010SH1134BINNYITTINNEDSORD4001/02/2018 03:03 FOODFOODFOODFOOD
SG54739Item2018101/01/2018NXGPET14SG11241201010SH1136DOG BZITTUNASORD5001/02/2018 03:03 FOODFOODFOODFOOD
SG54740Item2018101/01/2018NXGPET15SG11253201010SH1137BINNYITTINNEDSORD6001/02/2018 03:03 FOODFOODFOODFOOD
SG54741Item2018101/01/2018NXGPET18SG11281201010SH1140DOG BZITYAZOOSORD7001/02/2018 03:03 FOODFOODFOODFOOD

 

 

As you can see we have multiple SALES_ORDER_NO's. Some of those sales orders have more than 1 row on the same SALES_ORDER_NO.

 

I need to find a way to be able to use a slicer or a filter so that i can find just examples where Manufacturer is YAZOO AND Hierarchy Level 3 = CONFIG

 

and then return all of the rows with the same SALES_ORDER_NUMBER, i.e in this example SALES_ORDER_NUMBER; SORD3 showing both SOURCE SOURCE_ENTRY_NO and entire rows for these lines so that i can calculate all of the values for these.

HI @ballist1x,

 

I was abble to do the presentation of the values based on slicers and this is working, based on the interactions between the tables.

 

However I have some addtional questions are you calculating KPI, charts etc, based on the selections? I'm asking this because the way the measure are calculated need to be inline with your selections.

 

To do this follow this steps:

  • 3 Slicers: Manufacturer; Hierarchy level 3; Sales order n.º
  • Edit interaction between slicers and table
    • Slicer Manufacturer:
      • Interact with: Hierachy Slicer ; Sales Order n.º Slicer
      • Don't interact with table
    • Slicer Hierarchy level3:
      • Interact with: Manufacturer Slicer ; Sales Order n.º Slicer
      • Don't interact with table
    • Slicer Sales order n.º:
      • Interact with: Manufacturer Slicer ; Sales Order n.º Slicer; Table

Based on this make a selection of manufacturer and hierarchy level, then select the orders in the sales order number and your table will be updated with all the lines you need:

 

Slicers.gif

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





@MFelix

 

thanks mfelix, thats the outcome that i want to get to but i want to be able to use a single filter or slicer to be able to pull the data to show for example the total revenue, margin, cost for those orders with Config listed in them by using a single selection.

 

this is why i wamted to write either DAX or Powerquery or create a new table that can basically find and display all of the orders that match that criteria.

 

for example, if i have the following visualisations, i need them to be filtered to show data where we have created the slicers as above but in one single query

 

is there a way i can upload an attachment?DATA111.png

 

Hi @ballist1x,

 

I have been looking at your question and looking at the setup and with making some "ugly" coding you can do the following:

 

  • Create a new table with the following code:
Orders_Config_Summary =
FILTER (
    SUMMARIZE (
        ALL ( Table1[MANUFACTURER]; Table1[Hierarchy Level 3]; Table1[SALES_ORDER_NO] );
        Table1[SALES_ORDER_NO];
        Table1[MANUFACTURER];
        [Hierarchy Level 3]
    );
    Table1[MANUFACTURER] = "YAZOO"
        && Table1[Hierarchy Level 3] = "CONFIG"
)
  • Create a calculated column on the Data table with the following code:
Config_Data =
IF (
    LOOKUPVALUE (
        Orders_Config_Summary[SALES_ORDER_NO];
        Orders_Config_Summary[SALES_ORDER_NO]; Table1[SALES_ORDER_NO]
    )
        = BLANK ();
    "NOCONFIG";
    Table1[SALES_ORDER_NO]
)
  • Add a slicer with the config_data column to your report
    • Having no selection will give you the unfiltered data having a selection will get the orders with the config in it

 

Again this is very "ugly" coding and it's hard coded in the summary table the manufacturer and Hierarchy level, don't know if you need to have any changes of this to another manufacturer or hierarchy configuration but it's working with the information you gave.

 

Slicers_.gif

 

See attach  the pbix file (we tranfers only available for 7 days) in page 2 is this solution,

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

@MFelix

 

Hi, i now have this working as you described and created the extra table.

 

Now what i need to do is to go back to table1, and say if SORD is in the new table Orders_Config_Summary then put a YES into a new column in table1 next to every entry number row where that SORD matches the SORD in the Orders_Config_Summary...

 

 

Hi @ballist1x,

 

Add the following calculated column:

 

SORD OK =
IF (
    ISBLANK (
        LOOKUPVALUE (
            Orders_Config_Summary[SALES_ORDER_NO];
            Orders_Config_Summary[SALES_ORDER_NO]; Table1[SALES_ORDER_NO]
        )
    );
    "NO";
    "YES"
)

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Hi @ballist1x,

 

You can add a file trhough a link of onedrive, google drive or wetransfer.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





it okay it allowed me to attach a pic.

 

thats the kind of dashboard i have and i need to find a way to use a filter to simply show me ALL of the orders, or ALL of the orders that contain one entry number which includes Manufacturer Yazoo and Hierarchy 3 CONFIG

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors