Repeated excel rows based on a cell with multiple values

0 votes

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

enter image description here

which should be displayed as such

enter image description here

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?

Oct 20, 2022 in Others by Kithuzzz
• 28,900 points
116 views

1 answer to this question.

0 votes

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.

answered Oct 20, 2022 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer

Excel Define a range based on a cell value

Let's say that cells A1, A2, A3, ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 53,520 points
160 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Excel Function to Exclude rows based on certain values

One method is to combine the FILTER() ...READ MORE

answered Jan 23 in Others by narikkadan
• 53,520 points
44 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
3,014 views
0 votes
1 answer

Power Query : http POST request with form data

Try using Uri.BuildQueryString and Json.Document. let ...READ MORE

answered Dec 12, 2018 in Power BI by Upasana
• 8,620 points
7,440 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
1,099 views
0 votes
1 answer

Power Query : making HTTP POST request with form data

Try Using Uri.BuildQueryString and Json.Document let ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
4,810 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 53,520 points
206 views
0 votes
1 answer

Formula for inserting a thumbnail picture into excel cell, based on another cell's value

Here is a really excellent tutorial on ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 53,520 points
166 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP