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
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]
      • dennisdepoorter's avatar
        dennisdepoorter
        Helper I
        7 years ago

        Thanks for the reply...

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

         

        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)...