Forum Discussion
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 various products that have many caracteristics, including a range beginning and a range ending, and costs :
| Product | Caracteristics 1 | Caracteristics 2 | Range beginning | Range end | Cost |
| A | Z | 20 | 1 | 1 | 10 |
| B | G | 12 | 3 | 2 | 10 |
| C | D | 35 | 5 | 3 | 12 |
| A | E | 40 | 8 | 8 | 30 |
What I want to do is to create a new table that would have the product information, its caracteristics, but with ligns for every number between the beginning and ending range, and that would divide the cost equally between each range.
| Product | Caracteristics 1 | Caracteristics 2 | Range | Cost |
| A | Z | 20 | 1 | 10 |
| B | G | 12 | 3 | 5 |
| B | G | 12 | 2 | 5 |
| C | D | 35 | 5 | 4 |
| C | D | 35 | 4 | 4 |
| C | D | 35 | 3 | 4 |
| A | E | 40 | 8 | 30 |
I would appreciate any advice !
Thank you 🙂
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.
3 Replies
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.
Works prefectly, thanks Ashish_Mathur !
You are welcome.