Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
  • Ashish_Mathur's avatar
    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.