combinar dos datos divididos de una tabla

Un usuario Pregunto ✅

séptima luna

Hola,

Lo siento por mi terrible inglés.,

Tengo una tabla del formulario de Sharepoint como esta:

01.png

Luego uso la herramienta Dividir para dividir [Working Day] un [Tuning Day] archivado por nueva línea # (si)

y crear dos nuevas tablas

Nueva Tabla 1 por [Working Day]

02.png

Nueva tabla 2 por [Tuning Day]

03.png

Ahora quiero combinar estas tablas así (conectar por nombre de proyecto)

04.png

Pero lo que realmente obtengo

05.png

¿Puedo obtener resultados como este?

04.png

¡Gracias!

Ashish_Mathur

Hola,

Este código M funciona. He usado la primera tabla como una entrada en sí misma.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Working Day", type text}, {"Tuning Day", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
    #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Project Name", "Attribute"}, "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns1",{"Attribute.1"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Project Name", "Attribute"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Index"}, {"Value", "Index"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Expanded Partition", "Merged", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged.1", type text}, {"Merged.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Merged.2]), "Merged.2", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Working Day", type number}, {"Tuning Day", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Merged.1", "Project Name"}})
in
    #"Renamed Columns"

Espero que esto ayude.

Sin título.png

Ashish_Mathur

Hola,

Este código M funciona. He usado la primera tabla como una entrada en sí misma.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Working Day", type text}, {"Tuning Day", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
    #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Project Name", "Attribute"}, "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns1",{"Attribute.1"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Project Name", "Attribute"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Index"}, {"Value", "Index"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Expanded Partition", "Merged", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged.1", type text}, {"Merged.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Merged.2]), "Merged.2", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Working Day", type number}, {"Tuning Day", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Merged.1", "Project Name"}})
in
    #"Renamed Columns"

Espero que esto ayude.

Sin título.png

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *