Transform specific numbers into rows

[MaximeG]

Hello,

In order of making data viable for analysis, I have to transform data coming from a singular value to multiple rows.

For example:

Country - SampleSize - Vaccinated

{Belgium - 5 - 2 , 

France - 3 - 1}

This data should become as following:

Belgium - Vaccinated

Belgium - Vaccinated

Belgium - Not-vaccinated

Belgium - Not-vaccinated

Belgium - Not-vaccinated

France - Vaccinated

France - Not-vaccinated

France - Not-vaccinated

I have been trying to make a for-loop function with SampleSize as the 'i', but I can't wonder if there isn't a more efficient way of doing so.

Hope you can help me and thanks!

[MaximeG]

Thank you very much for your help Pete!

The solution was very understandable and works perfectly.

[BA_Pete · Accepted solution]

Hi @MaximeG ,

In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkrNSc8szVXSUTIFYiOlWJ1opbSixLzkVCDXGIgNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [country = _t, sample = _t, vaccinated = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"country", type text}, {"sample", Int64.Type}, {"vaccinated", Int64.Type}}),
    addUnvaccinated = Table.AddColumn(chgTypes, "unvaccinated", each [sample] - [vaccinated]),
    unpivotOthCols = Table.UnpivotOtherColumns(addUnvaccinated, {"country", "sample"}, "Attribute", "Value"),
    addList = Table.AddColumn(unpivotOthCols, "list", each {1..[Value]}),
    expandList = Table.ExpandListColumn(addList, "list"),
    remOthCols = Table.SelectColumns(expandList,{"country", "Attribute"})
in
    remOthCols

SUMMARY:

1) Add column with number of unvaccinated.

2) Unpivot [vaccinated] and [unvaccinated] columns

3) Add column creating list between 1 and vax/unvax value.

4) Expand list to duplicate rows.

Pete