Forum Discussion
Split one row into two rows
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]
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)...
what's the definiton of the [Measure]?
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.
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