Log In
Register
Home
August 21, 2019
Offline
Hi
not sure is it possible to achieve this in using power query, had been attempting for a while however couldn't find a innovative way, or probably to remove null in between and stack it up individually, it can use excel way to achieve it with several ways (small, row, countA etc), just curious if power query can do it too
Thank you !
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
There are no limits, you can do anything.
Here is an example:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Headers = Table.ColumnNames(Source),
Columns = Table.ToColumns(Source),
Sort = List.Transform(Columns, each List.Sort(List.RemoveNulls(_), Order.Ascending)),
Tbl = Table.FromColumns(Sort,Headers)
in
Tbl
But...
The structure is wrong, you should use a tabular structure with expanding rows, not columns. Whenever you need to add new columns for new data, this is a good indicator that the structure is wrong. Should be:
| Team | Name |
| Team A | David |
| Team A | Jerry |
| Team A | John |
| Team A | Ronald |
| Team B | Albert |
| Team B | Andy |
| Team C | Chris |
| Team C | Jane |
| Team C | Lawrence |
Answers Post
August 21, 2019
Offline
Hi Catalin, Thanks ! you are the expert
By the way, table1 is taken from original source by users, and usually they do not want to change the "Structure" and as long as it is able to use PQ, and it is more efficient than excel, there is another option to achieve what they want...
Thank you very much !
1 Guest(s)
