Expand multiple columns in power query

[JeremyLeee]

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

[tsg1225]

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}))

[Kun]

What would the "ColumnName" should refer?

[ImkeF]

Hi @tsg1225 ,

if my understanding is correct, this should work for you:

Table.ExpandRecordColumn(#"<previous step>", "ColumnName", Record.FieldNames(Record.Combine(#"<previous step>"[ColumnName])))

It expands all record fields existing in one column dynamically.

[tsg1225]

Hi @ImkeF,

That worked beautifully, thank you so much Imke, really appreciate it.

[v-piga-msft]

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

[v-piga-msft]

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

[ImkeF · Accepted solution]

Hi @JeremyLeee ,

1. Check the column(s) that don't contain data to be expanded (in your case it looks just like "Column1.timestamp"
2. Rightclick your mouse and click "Unpivot other columns"
3. Expand "Value" from the new "Value" column
4. 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 
5. 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.