Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Split column into multiple rows and equally divide a value grouped by the splitted column

Hi everyone,    I would like to split a column with a delimeter into multiple rows and divide the value by the count of rows splitted into.    Like in a screenshot I attached , I would like to sp...
Need Help
LivioLanzo's avatar
LivioLanzo
Icon for Solution Sage rankSolution Sage
7 years ago

You easily do it like this:

 

let
    Source = Table.FromRecords( 
                    { [Continent = "Europe", Country = "Germany", City="Munich,Berlin,Cologne", Sales=300] }, 
                    type table [Continent = Text.Type, Country = Text.Type, City = Text.Type, Sales = Number.Type] ),
    
    Trnsfm = Table.TransformColumns( Source, {"City", each Text.Split(_,","), type list}),
    #"Added Custom" = Table.AddColumn(Trnsfm, "Sales2", each [Sales] / List.Count( [City] ), type number ),
    #"Expanded City" = Table.ExpandListColumn(#"Added Custom", "City"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded City",{"Sales"})
in
    #"Removed Columns"