Dynamically Re-Order Columns in Power Query
Вставка
- Опубліковано 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! - Наука та технологія
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😅
Brilliant. Absolutely brilliant. Thank you. So helpful
This solves a problem I am dealing with right now. Thanks!
This is awesome! Brilliant as always.
Thank you! This was so helpful!
Extremely useful as always Chandeep! No hard-coded column names is the key to flexible models.
Great video as usually! Thanks a lot
Very clever , thanks Chandeep!
Very interesting and useful. Thank you so much.
Great lesson, thank you
Brilliant, thank you
Excellent explanation. Thank you Chandeep!!
Really smart solution and clear explanation.
Very creative!
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.
Amazingly helpful
Awesome Tutorials
Lovely stuff
With a self reference from the new table on the sheet, it is possible to keep it dynamic when further columns get added.
My God!!! This was something I was looking for
Awesome ❤
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)
always best👍
Excellent video, it's hard to find high quality M content. Pleasure meeting you @ GES, great presentation!
Great
How is this awesome ❤❤❤
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.
@goodly if you know Hindi , please share, it very useful understanding lots of people
www.youtube.com/@desigoodly
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?
❤❤
I was thinking of purchasing your power query course. Is your courses regularly updated?
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?
Hi,
Could you make a video how we can dynamic expand monthly aggregated columns when we are merging queries, Regards
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!!
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.
👋👋👋👍👍👍👍
Very cool! BTW, the download link isn't working.
goodly.co.in/dynamic-column-re-ordering-power-query
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.
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