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 !
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 |
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 !
