Forum Discussion
Anonymous
4 years agoNot applicable
Create new table using ranges
Hi everyone, I am having some troubles with this issue and after many failed tests I am reaching to you. I feel like I should use PowerQuery here but I am not sure. I have a table with variou...
- 4 years ago
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Caracteristics 1", type text}, {"Caracteristics 2", Int64.Type}, {"Range beginning", Int64.Type}, {"Range end", Int64.Type}, {"Cost", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each {Number.From([Range end])..Number.From([Range beginning])}), #"Expanded Range" = Table.ExpandListColumn(#"Added Custom", "Range"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Range",{"Range beginning", "Range end"}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Caracteristics 2", type text}}, "en-IN"),{"Product", "Caracteristics 1", "Caracteristics 2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"), #"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Merged=text, Cost=nullable number, Range=number]}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Cost", "Range"}, {"Cost", "Range"}), #"Added Custom1" = Table.AddColumn(#"Expanded All", "Custom", each [Cost]/[Count]), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Count", "Cost"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}, {"Merged.3", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "Product"}, {"Merged.2", "Characteristics 1"}, {"Merged.3", "Characteristics 2"}, {"Custom", "Cost"}}) in #"Renamed Columns"Hope this helps.
Ashish_Mathur
4 years agoSuper User
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Caracteristics 1", type text}, {"Caracteristics 2", Int64.Type}, {"Range beginning", Int64.Type}, {"Range end", Int64.Type}, {"Cost", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each {Number.From([Range end])..Number.From([Range beginning])}),
#"Expanded Range" = Table.ExpandListColumn(#"Added Custom", "Range"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Range",{"Range beginning", "Range end"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Caracteristics 2", type text}}, "en-IN"),{"Product", "Caracteristics 1", "Caracteristics 2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
#"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Merged=text, Cost=nullable number, Range=number]}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Cost", "Range"}, {"Cost", "Range"}),
#"Added Custom1" = Table.AddColumn(#"Expanded All", "Custom", each [Cost]/[Count]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Count", "Cost"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}, {"Merged.3", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "Product"}, {"Merged.2", "Characteristics 1"}, {"Merged.3", "Characteristics 2"}, {"Custom", "Cost"}})
in
#"Renamed Columns"
Hope this helps.
- Anonymous4 years agoNot applicable
Works prefectly, thanks Ashish_Mathur !
- Ashish_Mathur4 years agoSuper User
You are welcome.