Repeated excel rows based on a cell with multiple values

Question

The ultimate goal would be to adjust the report's granularity so that each row would be repeated X times with the appropriate ID on each row (where X is the number of IDs in one cell).

So data like this

which should be displayed as such

Is it possible to repeat each row using the appropriate IDs from the fourth column?

I experimented with things in Power Query Editor, but the best solution I could come up with was to add extra columns based on how many IDs there are. 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?

Answer 1

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"

I hope this helps you.