Create new table using ranges

[Anonymous]

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 🙂 

[Ashish_Mathur · Accepted solution]

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.

[Anonymous]

Works prefectly, thanks @Ashish_Mathur !

[Ashish_Mathur]

You are welcome.