Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hola a todos
He estado luchando con el siguiente ejercicio de mi equipo que quiero tener sus pensamientos expertos!
Somos un minorista, vendemos múltiples tipos de productos, incluyendo Single y Bundle (combinación de múltiples productos individuales)
Ex:
- Sencillo: Un ratón
- Paquete: (01 x ratón)+ (02 x USB)
Al final, no queremos ver el total de ventas de ese paquete solamente, necesitamos "de-bundle", lo que significa que necesitamos dividir las Ventas en cada componente en el informe final. Ilustración como se muestra a continuación. ¿No sé si esto es algo que se puede hacer en Power BI?
¡Gracias por ayudar!
Solved! Go to Solution.
@duycao , Mi sugerencia fusionaría estos dos usando la combinación izquierda en la consulta de poder
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
No @duycao,
Puede convertir la consulta al editor de consultas ouput de ouput esperado:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY4xCsAgDEXvklloEhXs6hU6SreWUihuvX/zdagiwZj8x7MUUlZZ7Cg52pgF112v57QGs7YQxotpdz/gbZSZsc5vPRoQrXxANoEKMxG6QkcFCIF0tQpxysee92O+/aQBMCQT7B8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Product ID" = _t, #"Single/Bundle" = _t, Items = _t, #"Original price" = _t, #"Paid Price" = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product ID", type text}, {"Single/Bundle", type text}, {"Items", Int64.Type}, {"Original price", Int64.Type}, {"Paid Price", Int64.Type}, {"Sales", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Product ID"}, #"Bundle lookup", {"Product ID"}, "Bundle lookup", JoinKind.LeftOuter),
#"Expanded Bundle lookup" = Table.ExpandTableColumn(#"Merged Queries", "Bundle lookup", {"Component ID", "Quantity in Bundle"}, {"Bundle lookup.Component ID", "Bundle lookup.Quantity in Bundle"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Bundle lookup", "Product ID 2", each if Text.Contains([Product ID], "S") then [Product ID] else if Text.Contains([Product ID], "B") then [Bundle lookup.Component ID] else null),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "items2", each [Items]*([Bundle lookup.Quantity in Bundle]+1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"items2"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,1,Replacer.ReplaceValue,{"Bundle lookup.Quantity in Bundle"}),
#"Added Custom1" = Table.AddColumn(#"Replaced Value", "Items2", each [Items]*[Bundle lookup.Quantity in Bundle]),
#"Merged Queries1" = Table.NestedJoin(#"Added Custom1", {"Product ID 2"}, #"Added Custom1", {"Product ID"}, "Added Custom1", JoinKind.LeftOuter),
#"Expanded Added Custom1" = Table.ExpandTableColumn(#"Merged Queries1", "Added Custom1", {"Paid Price"}, {"Added Custom1.Paid Price"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Added Custom1", "Custom", each [Items2]*[Added Custom1.Paid Price]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Product ID", "Single/Bundle", "Items", "Original price", "Paid Price", "Sales", "Bundle lookup.Component ID", "Bundle lookup.Quantity in Bundle", "Added Custom1.Paid Price"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Product ID 2", "Product ID"}, {"Items2", "Items"}, {"Custom", "Sales"}})
in
#"Renamed Columns"
Para obtener más información, consulte el archivo pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVZ8RWjHyBhGhMMqTg...
Si este post ayuda, entonces considera Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.
Saludos
Dedmon Dai
No @duycao,
Puede convertir la consulta al editor de consultas ouput de ouput esperado:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY4xCsAgDEXvklloEhXs6hU6SreWUihuvX/zdagiwZj8x7MUUlZZ7Cg52pgF112v57QGs7YQxotpdz/gbZSZsc5vPRoQrXxANoEKMxG6QkcFCIF0tQpxysee92O+/aQBMCQT7B8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Product ID" = _t, #"Single/Bundle" = _t, Items = _t, #"Original price" = _t, #"Paid Price" = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product ID", type text}, {"Single/Bundle", type text}, {"Items", Int64.Type}, {"Original price", Int64.Type}, {"Paid Price", Int64.Type}, {"Sales", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Product ID"}, #"Bundle lookup", {"Product ID"}, "Bundle lookup", JoinKind.LeftOuter),
#"Expanded Bundle lookup" = Table.ExpandTableColumn(#"Merged Queries", "Bundle lookup", {"Component ID", "Quantity in Bundle"}, {"Bundle lookup.Component ID", "Bundle lookup.Quantity in Bundle"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Bundle lookup", "Product ID 2", each if Text.Contains([Product ID], "S") then [Product ID] else if Text.Contains([Product ID], "B") then [Bundle lookup.Component ID] else null),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "items2", each [Items]*([Bundle lookup.Quantity in Bundle]+1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"items2"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,1,Replacer.ReplaceValue,{"Bundle lookup.Quantity in Bundle"}),
#"Added Custom1" = Table.AddColumn(#"Replaced Value", "Items2", each [Items]*[Bundle lookup.Quantity in Bundle]),
#"Merged Queries1" = Table.NestedJoin(#"Added Custom1", {"Product ID 2"}, #"Added Custom1", {"Product ID"}, "Added Custom1", JoinKind.LeftOuter),
#"Expanded Added Custom1" = Table.ExpandTableColumn(#"Merged Queries1", "Added Custom1", {"Paid Price"}, {"Added Custom1.Paid Price"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Added Custom1", "Custom", each [Items2]*[Added Custom1.Paid Price]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Product ID", "Single/Bundle", "Items", "Original price", "Paid Price", "Sales", "Bundle lookup.Component ID", "Bundle lookup.Quantity in Bundle", "Added Custom1.Paid Price"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Product ID 2", "Product ID"}, {"Items2", "Items"}, {"Custom", "Sales"}})
in
#"Renamed Columns"
Para obtener más información, consulte el archivo pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVZ8RWjHyBhGhMMqTg...
Si este post ayuda, entonces considera Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.
Saludos
Dedmon Dai
@duycao , Mi sugerencia fusionaría estos dos usando la combinación izquierda en la consulta de poder
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
2 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
2 | |
2 | |
2 | |
1 | |
1 |