Forum Discussion

informer's avatar
informer
Helper I
1 year ago
Solved

PWQ - List.Accumulate + Table.TransformColumns for expanding mutiple values fields

Hi,

 

I want to expand mutiple values columns by creating as many new lines as there are multiple values in a field. An illustration is worth more than words

 

My initial data set

 

Cost Item|Value|Location|Business|Department

Sales ERP|1250000|India, UK, USA, Aus|Trading, Heavy Equipment|Sales, Finance, Ops

Country Wide Training|500000|India, USA|Consulting, Trading|Sales, Finance, HR

Branding Expense|1250000|USA, UK|Heavy Equipment, Manufacturing|Sales

Country Wide Training|200000|India, Aus|Consulting|IT, Ops

Book Keeping Software|100000|India, USA, UK, Aus|Trading, Heavy Equipment, Consulting, Manufacturing|IT, Finance

Digital Classrooms Infra|2250000|India, USA, UK, Aus|Trading, Heavy Equipment, Consulting, Manufacturing|Sales, Finance, HR, IT, Legal, Ops

 

And result

 

My code was initialy below but I needed to hard code the name of columns in #"ColWithList" and next steps :

 

 

let
    Source = Excel.Workbook(File.Contents("C:\Data\Expand-Columns-to-Multiple-Rows-by-Delimiter.xlsx"), null, true),
    DataTable = Source{[Item="Data",Kind="Table"]}[Data],
    Typage = Table.TransformColumnTypes(DataTable,{{"Cost Item", type text}, {"Value", Int64.Type}, {"Location", type text}, {"Business", type text}, {"Department", type text}}),
    #"ColWithList" = let
        tblWithList = Table.TransformColumns(Typage,{{"Location", each Text.Split(_,",")}, {"Business", each Text.Split(_,",")},{"Department", each Text.Split(_,",")}}), anyOut = tblWithList 
    in anyOut,
    #"LocationExpand" = Table.ExpandListColumn(ColWithList, "Location"),
    #"BusinessExpand" = Table.ExpandListColumn(#"LocationExpand", "Business"),
    #"DepartmentExpand" = Table.ExpandListColumn(#"BusinessExpand", "Department")
in
    #"DepartmentExpand"

 

 

 But I wanted to use a generic list of columns instead of hard-coded ones and changed code #"ColWithList"  step as below

 

 

 #"ColWithList" = let
        lstColName = Table.ColumnNames(Typage) //, anyOut = lstColName
        ,lstFieldForExpand = List.Skip (lstColName, 2) //, anyOut = lstFieldForExpand
       ,tblWithList = List.Accumulate (lstFieldForExpand, "", (anyInitOut,anyCurr)=> Table.TransformColumns(Typage,{anyCurr, each Text.Split(_,",")})), anyOut = tblWithList
    in anyOut,

 

 But only the last column of lstFieldForExpand is transformed in a list

 

Thanks by advance for any help

  • Solution

     

    = let
            lstColName = Table.ColumnNames(Typage) //, anyOut = lstColName
            ,lstFieldForExpand = List.Skip (lstColName, 2) //, anyOut = lstFieldForExpand
            ,tblWithList = Table.TransformColumns( Typage,List.Transform(lstFieldForExpand, each {_, each Text.Split(_,",")})) , anyOut = tblWithList
        in anyOut

1 Reply

  • Solution

     

    = let
            lstColName = Table.ColumnNames(Typage) //, anyOut = lstColName
            ,lstFieldForExpand = List.Skip (lstColName, 2) //, anyOut = lstFieldForExpand
            ,tblWithList = Table.TransformColumns( Typage,List.Transform(lstFieldForExpand, each {_, each Text.Split(_,",")})) , anyOut = tblWithList
        in anyOut