Forum Discussion
Worthers
1 year agoNew Member
Duplicate rows based on a column value
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 G...
ronrsnfld
1 year agoSuper User
- 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
1 year agoNew Member
Thank you so much Ron, I will let you know how I get on.