Forum Discussion

dennisdepoorter's avatar
dennisdepoorter
Helper I
7 years ago

Split one row into two rows

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 combi...
Need Help
Stachu's avatar
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's avatar
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's avatar
    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

     

    • dennisdepoorter's avatar
      dennisdepoorter
      Helper I
      7 years ago

      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's avatar
        Stachu
        Community Champion
        7 years ago

        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]