Forum Discussion
Expand multiple columns in power query
Hi JeremyLeee ,
- Check the column(s) that don't contain data to be expanded (in your case it looks just like "Column1.timestamp"
- Rightclick your mouse and click "Unpivot other columns"
- Expand "Value" from the new "Value" column
- Ask yourself if you should dance the happy dance right now (because your data is in the shape that's actually best suited for the data model) or you first need to pivot it back
- optional: If you actually want to bring back the values into one column each, check the new "Attribute" column -> on the Transform tab, choose "pivot column" -> choose "Value" in the field "Values column"
If the execution time of this query takes too long, I might have a quicker solution, but that requires some coding.
I see in the screenshot above, multiple records in a column. Is there a function or a set that would allow to retrieve all rows records without having to use the “two arrows” button which only expands to create a static set of columns? I’d appreciate the help here. Also, tried a couple of these but stuck at only able to get the record data for the top row:
option 1:
= Table.ExpandRecordColumn(<previous step>, "ColumnName", Table.ColumnNames(Table.FromRecords(Table.Column(<previous step>, "ColumnName"))), Table.ColumnNames(Table.FromRecords(Table.Column(<previous step>, "ColumnName"))))
option 2:
= Table.ExpandRecordColumn(#"<previous step>", "ColumnName", Record.FieldNames(#"<previous step>"[ColumnName]{0}), Record.FieldNames(#"<previous step>"[ColumnName]{0}))
What would the "ColumnName" should refer?