Split one row into two rows

[dennisdepoorter]

Hi All,

I have an issue, and don't know if it is even possible:

We have some products we sell and some combination of products we sell.

For a production dashboard, I have to split some combined products back to the single product.

The combined products are

ACN for NORMAL TO OILY SKIN is a combination of:

• 1 ACN cream
• 1 ACN Wash

ACN+ for DRY SKIN is a combination of:

• 1 ACN+ cream
• 1 ACN+ wash

So we make a production assumption based on LxxD, Order quantity, current stock and safety stock. The problem is, that we should split up de combined products in their single product.

So in the example above, we should hide de row ACN for NORMAL TO OILY SKIN (673 products), but increase ACN cream with 673 products and ACN Wash with 673 products.

Is there a way to do this?

Thanks!!!

[Stachu]

you need a flag in your Product dimension  telling if product is a component or combination

or you can create mini hierarchy in the data - only report the component products, with combinations being the higher level in Product dimension

one way or the other you will need to change your tables

[dennisdepoorter]

Hmmmmm.... thanks for the quick answer... the problem is that the date comes out Salesforce,... so the table can't be changed.

If there is any other way... else I have to check for another solution...

Thanks,

Dennis

[Stachu]

could it be that this field is availabel in the system, but is not extracted? right now it's basically doublecounting, so I would be surprised if the system didn't have option to avoid that

otherwise, if there is a logic that you can apply to identify all this products then we can have a look into implementing it

examples that you describe make sense for a person, but are not structured enough to write some code, e.g. I have no clue if it works for SKN products

you could always do the mapping manually and blend it with your SalesForce data

[dennisdepoorter]

Hi Stachu,

In Salesforce, we created the packages also as 1 product, so it's not in the system (at this moment).

At this moment (en at least for the comming 24 months), we only have 2 packages:

• ACN+ for DRY SKIN
  • 1 ACN+ Wash
  • 1 ACN+ Cream
• ACN for NORMAL TO OILY SKIN
  • 1 ACN Wash
  • 1 ACN Cream

So, I hope this creates an opportunity *cross the fingers*

[Stachu]

so it's basically custom mapping solution

in Query Editor create a new query and paste the following code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnT201ZIyy9ScAmKVAj29vRT0oGIhScWZyjF6uBR4VyUmpgLUwJW4ecf5OvooxDir+Dv6YOsGMU0AkqhxsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Combined = _t, Single = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Combined", type text}, {"Single", type text}})
in
    #"Changed Type"

it would be better if you use product codes rather than names

merge your data table with the one above, with left outer join on Name -> Combined (or on respective product codes )

expand the name (or code) of single Products

in your data table create a new column, with following code (again you may want to replace [Name] with the product code

if [Single] = null then [Name] else [Single]

[dennisdepoorter]

Thanks for the reply...

I made some changes in Salesforce and added a extra table (product component) --> make us more future proof

The problem is now, I don't get my quantity how I want it... He gives for every product the same quantity instead of the quantity for each product (see picture below)...

[Stachu]

what's the definiton of the [Measure]?

[dennisdepoorter]

Quantity = SUM('Order Product'[Quantity])

De sum of quantity on order product (where order product is the table wit all the orders on product level),... but don't know if this will work.

[Stachu]

that's because the relation between 'Order Product' and 'Product Component' is many to many

what I had in mind is expanding the 'Order Product' table to only contain singles, that means there is only relation with Product, which is 1:many, and 'Product Component' doesn't have to loaded to the model at all

[dennisdepoorter]

The problem is,... al that data comes out of Salesforce, so I don't think / know it's that easy???

[Stachu]

it's imported model, right? if so then you can do pretty much anything with the data - add custom columns, remove unnecessary columns etc.
the only question is performance of the refresh (doesn't affect the end user though)

[dennisdepoorter]

Can someone help me out here... still stuck.

So what is the problem:

• We have 10 different products
  • ACN Cream
  • ACN+ Cream
  • ACN Wash
  • ACN+ Wash
  • SKN Wash
  • SKN Body Cream
  • SKN Face Cream
  • SKN Hydra + Face Cream
  • VGN Wash
  • FNG Spray
• We also have 2 products that are a combination of 2 of our 10 products
  • ACN Therapy
    • ACN Wash
    • ACN Cream
  • ACN+ Therapy
    • ACN+ Wash
    • ACN+ Therapy

We want to have a production report, but in the data we get from Salesforce, it's not possible to split up the Therapy boxes again in single products. Therefore, I hope there is another way to do it. We want to split up every ACN Therapy and ACN+ Therapy again in the single products so we know what we have to produce when.

@Stachusuggested to do it with a query, but I'm stuck there. So can someone help me out

I made a test environment with test data: TEST Environment

THANKS!!!!

[dennisdepoorter]

As I didn't get any clear answer to my problem... I think it's not possible in powerBI... correct me if I'm wrong, but I just hit the powerBI-wall

Because it's possible in Einstein Analytics (Salesforce Wave Analytics), I'm considering to make the switch to Einstein Analytics because of its functionalities, and less limitations then powerBI. Still hope someone comes up with a solution for my problem.

Best regards,

Dennis

[Stachu]

it's not really a PowerBI wall - it's possible, but requires changes in your model (at least based on your explanation)

you work with Salesforce data so tool customised for Salesforce most likely it will be better match for you, especially if it  has access to some product flags hidden for PowerBI

[dennisdepoorter]

Hi @Stachu,

I tried to fix it like you told with a querie, but it just didn't worked out (I have no knowledge about queries in PowerBI so fare... )

If it helps for you, I have a test inviroment of the database online:

You can find the test environment here (dropbox): Test environment

Can you / someone have a look please?

Thanks!!!