Explosion de datos

Question

Hola,&nbsp;Trabajo para una coorporación de manufactura, y necesitamos empezar a medir los consumos de accesorios por cada prenda costurada.&nbsp;Tengo 2 datas:1-DATA BOM: Esta data es la base donde me refleja: construcción,estilo, color, talla, los accesorios necesarios para costurar dicho estilo, y su consumo respectivo como por ejemplo&nbsp;CX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Estilo&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Color&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Talla&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Accesorio&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ConsumoSS BASIC TEE AB TA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PC54&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LEMON YELLOW&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;XL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CAJA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.02778SS BASIC TEE AB TA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PC54&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LEMON YELLOW&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;XL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CASE LABES&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.02778SS BASIC TEE AB TA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PC54&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LEMON YELLOW&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;XL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;LABEL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1SS BASIC TEE AB TA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PC54&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LEMON YELLOW&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;XL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;LANA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.00373SS BASIC TEE AB TA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PC54&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LEMON YELLOW&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;XL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ROBBON&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.00002SS BASIC TEE AB TA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PC54&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LEMON YELLOW&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;XL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TAPE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.00047&nbsp;DATA 2 PRODUCCIÓN, Está data me refleja la planta que realizo la producción, construcción, estilo, color, talla, cantidad en piezas, cantidad en docenas. Como por ejemplo.&nbsp;Planta&nbsp; &nbsp; &nbsp;CX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Estilo&nbsp; &nbsp; &nbsp; &nbsp; Color&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Talla&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Cant piezas&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Cant docenas5 star&nbsp; &nbsp; &nbsp; SS BASIC TEE AB TA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PC54&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;LEMON YELLOW&nbsp; &nbsp; &nbsp; XL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 166.66&nbsp;&nbsp;Necesito hacer una explosión de datos para que en la data 2, me desgloce los accesorios de dicho estilo, la cantidad en piezas debe ser repetitiva para cada linea para calcular el consumo real. por ejemplo&nbsp;&nbsp;Para calcular el consumo es:Cantidad de piezas producidas * el consumo del accesorios.&nbsp;&nbsp;Favor si alguien me dar un solución, se lo agradeceria.&nbsp;Saludos.

pmreis · Accepted Answer

Hi&nbsp;Selvin&nbsp;It seems you don't have ID's which can be used directly to link the tables. You you have them please use them. If not, you can use a strategy which leverages composite keys.&nbsp;Create Composite Keys:In both datasets, create a new column that concatenates the product attributes to form a unique key for each product.Key = DATA BOM[CX] &amp; "-" &amp; DATA BOM[Style] &amp; "-" &amp; DATA BOM[Color] &amp; "-" &amp; DATA BOM[Size]Relate the Tables:Use the newly created keys to establish a relationship between the tables in the "Model" view.Calculate Actual Consumption:Once the tables are related, you can create a new column in the production table to calculate the actual consumption:Actual Consumption = DATA 2 PRODUCTION[Pieces Quantity] * RELATED(DATA BOM[Consumption])Visualize:Use the tables to create visuals in Power BI to analyze the actual accessory consumption.Creating a composite key based on product attributes ensures that you can link the tables accurately, even if there's no pre-existing unique identifier. Let me know if it worked!Pedro Reis - Data Platform MVP / MCTMaking Power BI and Fabric SimpleIf my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!Find me at&nbsp;LinkedIn

Forum Discussion

Explosion de datos

1 Reply

Related content

BOM Explosion Logic Creation

BOM (Bill of materials) explosion in Power BI

BOM explosion inside BOM

Fuente de datos

BREAKING NEWS! - Minifigure Population Explosion!