Forum Discussion
How can I use Power Query to expand a cell into several rows?
Essentially, this is what I have:
| Year | County | City | fatalities | percent | Emphasis_Category | Emphasis_Area | |
| 2010 | Adams | Adams-Unincorporated | 6 | 1.000 | Total | Total | |
| 2010 | Benton | Benton-Unincorporated | 9 | 1.000 | Total | Total |
And this is what I want to have:
| Year | County | City | fatalities | percent | Emphasis_Category | Emphasis_Area |
| 2010 | Adams | Adams-Unincorporated | 1 | 1.000 | Total | Total |
| 2010 | Adams | Adams-Unincorporated | 1 | 1.000 | Total | Total |
| 2010 | Adams | Adams-Unincorporated | 1 | 1.000 | Total | Total |
| 2010 | Adams | Adams-Unincorporated | 1 | 1.000 | Total | Total |
| 2010 | Adams | Adams-Unincorporated | 1 | 1.000 | Total | Total |
| 2010 | Adams | Adams-Unincorporated | 1 | 1.000 | Total | Total |
| 2010 | Benton | Benton-Unincorporated | 1 | 1.000 | Total | Total |
| 2010 | Benton | Benton-Unincorporated | 1 | 1.000 | Total | Total |
| 2010 | Benton | Benton-Unincorporated | 1 | 1.000 | Total | Total |
| 2010 | Benton | Benton-Unincorporated | 1 | 1.000 | Total | Total |
| 2010 | Benton | Benton-Unincorporated | 1 | 1.000 | Total | Total |
| 2010 | Benton | Benton-Unincorporated | 1 | 1.000 | Total | Total |
| 2010 | Benton | Benton-Unincorporated | 1 | 1.000 | Total | Total |
| 2010 | Benton | Benton-Unincorporated | 1 | 1.000 | Total | Total |
| 2010 | Benton | Benton-Unincorporated | 1 | 1.000 | Total | Total |
Hi streborxam
Please refer to the following steps .
(1)Add a custom column .
(2)Expand the custom column .
(3)Remove the column [fatalities] and add a custom column rename it with same name [fatalities]
(4) Remove the column [custom] and reorder the column [fatalities] in [City] and [percent] .
I have attached my pbix file , you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- lbendlin
Super User
streborxam here is one option
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFDSUXJMScwthtG6oXmZecn5RQX5RYklqSlAYTMgNtQzMAApDckvScyB07E6cDOcUvNK8vPgDExTLHGbEgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ear = _t, County = _t, City = _t, fatalities = _t, percent = _t, Emphasis_Category = _t, Emphasis_Area = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"fatalities", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {1..[fatalities]}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"ear", "County", "City", "percent", "Emphasis_Category", "Emphasis_Area"}), #"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "fatalities", each 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"ear", "County", "City", "fatalities", "percent", "Emphasis_Category", "Emphasis_Area"}) in #"Reordered Columns" Hi streborxam
Please refer to the following steps .
(1)Add a custom column .
(2)Expand the custom column .
(3)Remove the column [fatalities] and add a custom column rename it with same name [fatalities]
(4) Remove the column [custom] and reorder the column [fatalities] in [City] and [percent] .
I have attached my pbix file , you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
