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 gurús de Power BI! Espero que puedas ayudarme a resolver este problema. Tengo una tabla de LDM de dos niveles, que no tiene ninguna clave de jerarquía padre-hijo. Mi objetivo final es crear la tabla WhereUsed, donde la primera columna enumeraría todos los componentes y la segunda columna enumeraría todos los elementos principales (omitiendo subensamblajes).
TABLA DE ENTRADA:
Material de los padres Material infantil
Producto 1 | Subensamblaje 1 |
Producto 1 | Componente 1 |
Producto 1 | Componente 2 |
Subensamblaje 1 | Componente 3 |
Subensamblaje 1 | Componente 4 |
Subensamblaje 1 | Componente 5 |
Subensamblaje 1 | Componente 6 |
TABLA DE SALIDA DESEADA:
Material infantil Padre de primer nivel
Componente 1 | Producto 1 |
Componente 2 | Producto 1 |
Componente 3 | Producto 1 |
Componente 4 | Producto 1 |
Componente 5 | Producto 1 |
Componente 6 | Producto 1 |
No hay @CSteff
Una forma de hacerlo en M, péguelo en el Editor Avanzado del Editor de Power Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlEwVNJRCi5NSiwuTs1NyqkE8mN1UGWd83ML8vNS80rwyhmB5VBNQpY3JiBvQkDelIC8mVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Parent Material" = _t, #"Child Material" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent Material", type text}, {"Child Material", type text}}),
ParentList = List.Buffer(#"Changed Type"[Parent Material]),
ChildList = List.Buffer(#"Changed Type"[Child Material]),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Top Level Parent", each List.Reverse( List.Generate(()=>
[child = [Child Material],
position = List.PositionOf(ChildList,child),
parent = if position = null then child else ParentList{position},
parents = parent],
each [parent] <> null,
each [child = [parent],
position = List.PositionOf(ChildList,child),
parent = try ParentList{position} otherwise null,
parents = parent ],
each [parents])){0}),
Custom1 = List.Difference(ChildList,ParentList),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Child Material"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Child Material"}, #"Added Custom", {"Child Material"}, "Custom", JoinKind.LeftOuter),
#"Expanded Top Level" = Table.ExpandTableColumn(#"Merged Queries", "Custom", {"Top Level Parent"}, {"Top Level Parent"})
in
#"Expanded Top Level"
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 | |
2 | |
2 | |
2 | |
1 |