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
• 38,010 points
1,077 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
• 63,420 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
• 63,420 points
2,612 views
0 votes
1 answer

Excel VBA search based on cell values into folders and sub-folders to get the file path and data

This will create a listing of all ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
1,585 views
0 votes
1 answer

VBA Change Cell colors based on value, and it can deal with single cell and multiple cells changes

Before looping through all of the cells ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,420 points
434 views
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, 2023 in Others by narikkadan
• 63,420 points
6,012 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,827 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
8,311 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,435 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
5,647 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
• 63,420 points
966 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
• 63,420 points
1,137 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