How can I use Power Query to expand a cell into several rows?

[streborxam]

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

[Anonymous · Accepted solution]

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.

[lbendlin]

@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"