I have a data set with Names in addresses in a stacked format. There is a column within the data set that indicates how many lines the record has. For example, some records have 3 lines, some have 4 ETC.
My initial thought was to use Power Query, add an index column, then use the Modulo function, but that seems to work for data that has a defined set number of rows.
Is there a way to unstack this data using the modulo function or some other means?
I have attached a sample of the data, please see attached.
Thank you for taking the time to review this. I have looked at the file, and I am unsure how different it is from what I am starting with?
I will add an ideal outcome to the file and repost an edited file.
That is correct. I need to unstack column_5, but column "Number-Of_Lines" will likely be the helper column to get an idea of how many pieces of information there are per record.
My apologies, I should have been more clear in my question.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], RemovedOtherColumns = Table.SelectColumns(Source,{"Column_1", "Number-Of_Lines", "Column_5"}), PivotedColumn = Table.Pivot(RemovedOtherColumns, List.Distinct(RemovedOtherColumns[#"Number-Of_Lines"]), "Number-Of_Lines", "Column_5"), RemovedColumns = Table.RemoveColumns(PivotedColumn,{"Column_1"}) in RemovedColumns
or formula
H3
=IFERROR(INDEX($E$3:$E$23,AGGREGATE(15,6,ROW($D$3:$D$23)/(AGGREGATE(15,6,ROW($D$3:$D$23)/($D$3:$D$23="1"),ROWS(H$3:H3))=LOOKUP(ROW($D$3:$D$23),ROW($D$3:$D$23)/($D$3:$D$23="1"))),COLUMNS($H3:H3))-ROW($D$2)),"")
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks