combine two Split data from one table

[7thmoon]

Hi,

Sorry for my terrible english,

I have one table from sharepoint form like this:

Then I using Split tool to split [Working Day] an [Tuning Day] filed by new line #(if)

and create two new table

New Table 1 by [Working Day]

New Table 2 by [Tuning Day]

Now I want to combine these tables like this (connect by Project Name)

But what i actually get

Can I get results like this?

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

This M code works.  I have used the first table as an input itself.

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"

Hope this helps.