Forum Discussion
Solved
combine two Split data from one table
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!
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.
1 Reply
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.