Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, everybody!
I usually do simple aggregation with Power Query, but I was asked to aggregate an irregular CSV file and I was in trouble.
In Excel's Power Query, as described later, I want to convert a CSV file in which all rows are not one row of data to a CSV file of one row and one data, but I can't get the method. Can someone please help me?
Source Table
----------------
1
apple, 2, 5
orange, 1, 10
grape, 2, 4
3
orange, 1.2, 20
4
apple, 2, 8
grape, 2.5, 6
(next...)
----------------
↓
----------------
1, apple, 2, 5
1, orange, 1, 10
1, grape, 2, 4
3, orange, 1,2, 20
4, apple, 2, 8
4, grape, 2.5, 6
(next...)
----------------
Solved! Go to Solution.
Here's one way to do this in Power Query:
let
//change next line to reflect actual data source
Source = Excel.CurrentWorkbook(){[Name="Table28"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
//remove blank rows
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Column1] <> null and [Column1] <> " "),
//create a "grouper" column
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
//check if cell contents are all digits
// if so then copy over the Index value else write null
// Then fill down
#"Added Custom" = Table.AddColumn(#"Added Index", "grouper", each if List.ContainsAll({"0".."9"},Text.ToList([Column1])) then [Index] else null, Int64.Type),
#"Filled Down" = Table.FillDown(#"Added Custom",{"grouper"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
//Group by the "grouper" column
#"Grouped Rows" = Table.Group(#"Removed Columns", {"grouper"}, {
//create a list of the first row in the subtable concatenated to each of the other lines
{"new csv", (t)=>List.Transform(List.RemoveFirstN(t[Column1],1), each t[Column1]{0} & ", " & _), type list}
}),
//Remove the "Grouper" column and expand the List column
#"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"grouper"}),
#"Expanded new csv" = Table.ExpandListColumn(#"Removed Columns1", "new csv")
in
#"Expanded new csv"
Here's one way to do this in Power Query:
let
//change next line to reflect actual data source
Source = Excel.CurrentWorkbook(){[Name="Table28"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
//remove blank rows
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Column1] <> null and [Column1] <> " "),
//create a "grouper" column
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
//check if cell contents are all digits
// if so then copy over the Index value else write null
// Then fill down
#"Added Custom" = Table.AddColumn(#"Added Index", "grouper", each if List.ContainsAll({"0".."9"},Text.ToList([Column1])) then [Index] else null, Int64.Type),
#"Filled Down" = Table.FillDown(#"Added Custom",{"grouper"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
//Group by the "grouper" column
#"Grouped Rows" = Table.Group(#"Removed Columns", {"grouper"}, {
//create a list of the first row in the subtable concatenated to each of the other lines
{"new csv", (t)=>List.Transform(List.RemoveFirstN(t[Column1],1), each t[Column1]{0} & ", " & _), type list}
}),
//Remove the "Grouper" column and expand the List column
#"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"grouper"}),
#"Expanded new csv" = Table.ExpandListColumn(#"Removed Columns1", "new csv")
in
#"Expanded new csv"
Wow, thanks ronrsnfld. I'm happy to get a reply so quickly.
I'm sorry I can't understand it quickly, but I'll read the reply I received.
I guess I was not familiar with Table.FillDown.
Thanks ronrsnfld, I got some very great tips. Appreciate it.
Happy to help
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.