Duplicate rows based on a column value

[Worthers]

I am new to Power BI query,  and I would if there was a way to duplicate rows based on a value in a column within the same row.

From this 

To something like this Medicine, Adult ED, 26 Very Good, 9 Good etc ...and then on to the next row  with the same principle:

Division	Specialty	MonthYY	Experience
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Very Good
Medicine	Adult ED	01/04/2022	Good
Medicine	Adult ED	01/04/2022	Good
Medicine	Adult ED	01/04/2022	Good
Medicine	Adult ED	01/04/2022	Good
Medicine	Adult ED	01/04/2022	Good
Medicine	Adult ED	01/04/2022	Good
Medicine	Adult ED	01/04/2022	Good
Medicine	Adult ED	01/04/2022	Good
Medicine	Adult ED	01/04/2022	Good

[v-mdharahman]

Hi @Worthers,

Thanks for reaching out to the Microsoft fabric community forum.

As @ronrsnfld and @Joe_Barry both provided responses of their own to your query, please go through the responses and mark the helpful response as solution.

I would also take a moment to thank @ronrsnfld and @Joe_Barry, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[Worthers]

Hi Joe,  thank you so much I will give your suggestion a try and let you know 

[ronrsnfld]

• Unpivot the Experience Columns
• Replace the new experience column with its description in a List repeated the number of times represented by the value.
• Remove the Value column
• Expand the List column "To Rows"
• Note the optional line in case you don't want to expand the Response column, which seems to be just a total of the number of the other responses. If you want to also expand that, merely delete that code step (in the UI).
• Also, you do not show what you want for a result in the event the response is "0". I delete that row, but you could include it with a blank in the Experience column by deleting that step (in the UI).

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Division", type text}, {"Specialty", type text}, {"Month", type date}, 
        {"Very Good", Int64.Type}, {"Good", Int64.Type}, {"Neither", Int64.Type}, 
        {"poor", Int64.Type}, {"very poor", Int64.Type}, {"Don’t Know", Int64.Type},
         {"Response", Int64.Type}}),

//Optional in case you don't want to include the Response totals in your repeated final output
    #"Removed Response Total" = Table.RemoveColumns(#"Changed Type",{"Response"}),

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Response Total", 
        {"Division", "Specialty", "Month"}, "Experience", "Value"),

    #"Repeats" = Table.ReplaceValue(
        #"Unpivoted Other Columns",
        each [Experience],
        each [Value],
        (x,y,z) => List.Repeat({y},z),
        {"Experience"}
    ),
    #"Removed Columns" = Table.RemoveColumns(Repeats,{"Value"}),

//Optional to remove rows with "0" responses
    #"Remove Empties" = Table.SelectRows(#"Removed Columns", each not List.IsEmpty([Experience])),
    #"Expanded Experience" = Table.ExpandListColumn(#"Remove Empties", "Experience")
in
    #"Expanded Experience"

Partial results:

[Worthers]

Thank you so much  Ron, I will let you know how I get on. 

[Joe_Barry · Accepted solution]

Hi @Worthers 

Highlight the columns I - O and rightclick and choose unpivot columns

Joe