Log In
Register
Home
August 16, 2019
Offline
In Power Query, I have a step where the column "Custom" stores table values and if I click the expand button it allows me to manually select the columns I want to show, but how can I dynamically select just the first and last columns only of the table values?
Moderators
January 31, 2022
Offline
Let's say you have table "Table1". Its first column is called "Ref" followed by a number of other columns.
Before grouping by "Ref", find the column names of the first and last 'other' columns (i.e. the 2nd and the last columns of the table).
Expand by using the variable names "first" and "last". Some example code below:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
first = List.First (List.Skip (Table.ColumnNames (Source),1)),
last = List.Last (Table.ColumnNames (Source)),
Group = Table.Group(Source, {"Ref"}, {{"Custom", each _, type table }}),
Expand = Table.ExpandTableColumn(Group, "Custom", {first, last})
in
Expand
If this doesn't make sense, please upload a file that demonstrates your actual situation.
Trusted Members
Moderators
November 1, 2018
Offline
Cross-posted here: https://www.mrexcel.com/board/.....n.1259256/
August 16, 2019
Offline
I was able to resolve it myself and used the following formula in a custom column. It looks at the "table" values in the Custom column if there is a single column it return that but if there are multiple columns it takes the first column and the last column, which is what I want.
However, when there are multiple columns it also takes the header of the first column (which is always Column1) and the last column i.e. Column4. Is there a way to amend the formula below so that it renames the last column to Column2 so that when I click the expand button the "table" values are either aligned under Column1 and Column2 names?
Table.SelectColumns([Custom],if Table.ColumnCount([Custom])=1 then {Table.ColumnNames([Custom]){0}} else {Table.ColumnNames([Custom]){0},Table.ColumnNames([Custom]){Table.ColumnCount([Custom])-1}})
August 16, 2019
Offline
@Velouria Looks like when I posted it was under "Awaiting Moderation" and when I clicked to delete it, it didn't delete the post. Not my fault. I was actually surprised it still posted. Perhaps you should work with the owner to see why the post doesn't get deleted when you click the delete button.
1 Guest(s)
