Repeated excel rows based on a cell with multiple values

nerdygeek 0
Viewed 32

The end goal would be to change the granularity of a report, where each row would be repeated X times (where X is the nr of IDs in one cell), with the relevant ID on each row

So data like this

enter image description here

which should be displayed as such

enter image description here

is there a way in which each row can be repeated, with the relevant IDs from the 4th column?

I tried something in Power Query Editor, however I only figured out a way to create more columns based on how many IDs there are - but its the ideal solution

I also found this article which is really helpful https://www.extendoffice.com/documents/excel/4054-excel-duplicate-rows-based-on-cell-value.html#a1 yet it only solves half of the problem, as it would only duplicate the rows based on how many IDs there are - how can this be done in a way that it actually populates the relevant ID too?

powerquery excel
1 Answers

You can use this query:

let
    Source = Excel.CurrentWorkbook(){[Name="Sheet1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "IDs", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Ids.1", "Ids.2", "Ids.3"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Name", "date", "detail"}, "Attribute", "ID"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

It first uses the "split column" function and then unpivots the table by keeping the first three columns.

You have to adjust the sheet name and the column names as well.

2nd option:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Ids", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Ids")
in
    #"Split Column by Delimiter"

Using the advanced options of the split column dialog and splitting into rows.

useryPy710
Related