Dynamically Re-Order Columns in Power Query
HTML-код
- Опубликовано: 6 фев 2024
- In this video, you will learn how to dynamically reorder columns in Power Query, whether you're working in Excel or Power BI. Learn how to create a user interface that allows you to define the positions of the columns, giving the user greater control and flexibility.
#powerquery #datacleaning #dataanalysis
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step-by-Step -
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️ - goodly.co.in/dynamic-column-r...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers! 
Наука
![Skilla Baby - Misfit (Feat. Polo G) [Official Video]](http://i.ytimg.com/vi/IjsylSnNMyw/mqdefault.jpg)
Download the file ⬇ - goodly.co.in/dynamic-column-re-ordering-power-query
Tackle even the most challenging data-cleaning problems. Check out the M Language course and push beyond the user interface ↗ - rb.gy/a2zsnn
Dear Chandeep! It was really a pleasure to meet you at GES today! As mentioned - you are best! Thank you for all your shared work!
What is GES?
Global excel summit
How did Chandeep teleport back home so fast? lol
@@YvesAustinM still in London. The video is was recorded earlier and scheduled😅
With a self reference from the new table on the sheet, it is possible to keep it dynamic when further columns get added.
This solves a problem I am dealing with right now. Thanks!
Extremely useful as always Chandeep! No hard-coded column names is the key to flexible models.
Hello Chandeep, I have been following your content for one and a half year, really want to do your course but the course I want to do is very expensive. But the really good stuff keeps it going.
Very interesting .Thank you . here is an other tip i'd like to share that gives the same result, using List.Sort and without using the user interface :
let
Input = Cols1,
Col = Input[Cols],
Pos = Input[Postion No],
ColSortingOrder = List.Reverse(
List.Sort(
Col,
(x, y) =>
Value.Compare(
let
Px = List.PositionOf(Col, x)
in
1 / Pos{Px},
let
Py = List.PositionOf(Col, y)
in
1 / Pos{Py}
)
)
)
in
Table.ReorderColumns(Personnalisé1, ColSortingOrder)
Brilliant. Absolutely brilliant. Thank you. So helpful
My God!!! This was something I was looking for
Thank you! This was so helpful!
This is awesome! Brilliant as always.
Very interesting and useful. Thank you so much.
Really smart solution and clear explanation.
This is very awesome! Thank you for creating/sharing!
I am curious what are your thoughts on first sorting by position number and then alphabetically sorting the first column to give some kind of pattern to the query should the column names change?
Great video as usually! Thanks a lot
Excellent explanation. Thank you Chandeep!!
Very clever , thanks Chandeep!
Great lesson, thank you
That was extremely useful. I have one question - will the order be maintained when loaded to Power BI. If not, how to maintain the column orders when data is loaded from Power Query to Power BI. Any guidance would be helpful.
Brilliant, thank you
Excellent video, it's hard to find high quality M content. Pleasure meeting you @ GES, great presentation!
Awesome Tutorials
Very creative!
Amazingly helpful
Lovely stuff
Great
Awesome ❤
always best👍
How is this awesome ❤❤❤
@goodly if you know Hindi , please share, it very useful understanding lots of people
www.youtube.com/@desigoodly
Hi,
Could you make a video how we can dynamic expand monthly aggregated columns when we are merging queries, Regards
❤❤
In Power BI it doesn't seem to matter what order you put the columns in. The table view sticks them in any order it feels like! Which sucks.
Do you have to refresh twice? First refresh will create the list for the user to type in the order and the next refresh will then do the job?
I was thinking of purchasing your power query course. Is your courses regularly updated?
How would this be impacted if column names change? Say % becomes Percent. Or a real world example, a list of employees. Someone changes their last name. Or someone leaves.
Yah good question
If the columns remain in the same order, this shouldn't affect the query, as long as you refresh the Cols-Query prior to loading the resulting query.
Very good video. Congfats!!
I have a problem, but a little diferent. There is a table with columnNames A, B, C and Another table without headers Column1, Column2, Column3. How can I append these tables?
The second table actually has column headers, but they are generic. You can simply rename them before appending.
let
Table1 = TableABC,
Table2 = TableC1C2C3,
RenamedHeadersTable2 = Table.RenameColumns(Table2, List.Zip({Table.ColumnNames(Table2), Table.ColumnNames(Table1)}),
AppendedTable2 = Table.Combine({Table1, RenamedHeadersTable2})
in
AppendedTable2
@@dabrain6944 thanks a lot!!
👋👋👋👍👍👍👍
Very cool! BTW, the download link isn't working.
goodly.co.in/dynamic-column-re-ordering-power-query
Instead of replacing null, I filter out null.
Then your columns will also disappear... Correct me if i am wrong...
No, they will not dissappear. But their order remains as it is. So it would really be better to filter out the nulls.
I meant rows...
They are actually not needed to make the query work.
I can’t believe you cant reorder the columns in go to columns. A simple move up or down button or drag and drop from the vertical list would be so much easier.
His technique is useful if you have a lot of columns to organize