Forum Discussion
Expand multiple columns in power query
Hi All,
Is that possible to expand 'value' for all the columns at once? I could do it one column by one column (see the expanded second column) but that is too slow.
Thanks in adance,
Jeremy
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.
7 Replies
Hi JeremyLeee ,
Normally, we could use Table.ExpandTableColumn to expand column in Power Query.
However, I'm afraid that we may not expand multiple columns at once with this function.
Hope ImkeF may have other suggestions for your requirement.
Best Regards,
Cherry
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.
Hi JeremyLeee ,
Have you tried with the suggestion of ImkeF ? Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
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?
