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 areACN for NORMAL TO OILY SKIN is a combination of:1 ACN cream1 ACN WashACN+ for DRY SKIN is a combination of:1 ACN+ cream1 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!!!

so it's basically custom mapping solutionin Query Editor create a new query and paste the following codelet 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 Productsin your data table create a new column, with following code (again you may want to replace [Name] with the product codeif [Single] = null then [Name] else [Single]

you need a flag in your Product dimension telling if product is a component or combinationor 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

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

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 itexamples 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

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 SKIN1 ACN+ Wash1 ACN+ CreamACN for NORMAL TO OILY SKIN1 ACN Wash1 ACN CreamSo, I hope this creates an opportunity *cross the fingers*

Split one row into two rows | Microsoft Fabric Community

15 Replies

Stachu
Community Champion
7 years ago
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
  Helper I
  7 years ago
  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
    Community Champion
    7 years ago
    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

Forum Discussion

Split one row into two rows

15 Replies

Related content

Split and transform columns into rows

Split values in one row into multiple

split rows

Splitting Column values

Split array into rows