Forum Discussion
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
- informerHelper I
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