Forum Discussion

7thmoon's avatar
7thmoon
New Member
6 years ago
Solved

combinar dos datos Split de una tabla

Hola

Lo siento por mi terrible inglés,

Tengo una tabla del formulario sharepoint como esta:

01.png

A continuación, utilizo la herramienta Dividir para dividir [Día de trabajo] un [Día de ajuste] archivado por la nueva línea (si)

y crear dos nuevas tablas

Nueva Tabla 1 por [Día de Trabajo]

02.png

Nueva Tabla 2 por [Día de sintonización]

03.png

Ahora quiero combinar estas tablas como esta (conectar por nombre del proyecto)

04.png

Pero lo que realmente obtengo

05.png

¿Puedo obtener resultados como este?

04.png

¡Gracias!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hola

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

    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.

    Untitled.png

1 Reply

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hola

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

    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.

    Untitled.png