Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
SebSchoon1
Post Patron
Post Patron

Dynamic reordering of columns

Hi Guys,

 

I would like to know if it is possible to reorder columns dynamically?

 

SebSchoon1_0-1653296507318.png

I have all these orders corresponding to different "Types" of items which have different sizes accross the tables.

 

I would like to keep the columns which are the same in the same order. and concerning the other columns (the sizes)

 

To be arranged like the initial order in the tables.

 

Any way to achieve this?

1 ACCEPTED SOLUTION

Here are examples of how you can dynamically set the column order.  If you can provide an example/screenshots of what the size column names are and the outcome you are trying to achieve I can provide a more specific solution. 

Match the column order for a different table:

Table.SelectColumns ( #"Name of Previous Step", Table.ColumnNames ( #"Other Table Name" ) )

Columns without "Size" in the name followed by columns with "Size" in the name:

Table.SelectColumns ( #"Name of Previous Step", let
   varBufferedList = List.Buffer ( Table.ColumnNames ( #"Name of Previous Step" ) ),
   varColumnsWithoutSize = List.Select ( varBufferedList, each not Text.Contains ( _, "Size" ) ),
   varColumnsWithSize = List.Select ( varBufferedList, each Text.Contains ( _, "Size" ) ),
   varFinalColumns = List.Combine ( {varColumnsWithoutSize, varColumnsWithSize} ) in
varFinalColumns ) 

 

 

View solution in original post

6 REPLIES 6
KT_Bsmart2gethe
Impactful Individual
Impactful Individual

Hi @SebSchoon1,

 

If the first table is already in the table you want.

 

a. Append tables with drill-down function:

1. Right-click on the header "Col" and select Drill Down

2. Add Text.Combine(Default step name). All tables will be appended according to the first table order.

 

If your first table required the headers to be promoted to the first rows.

1. Add a custom column: Table.PromoteHeaders([Col]).

2. do "a" above.

 

If the above solution is not what you are after. You can try the formula below.

1.  Add a custom column: Table.ReorderColumns([Col], Table.ColumnNames(#"Previous Step Name"{0}[Col]))

 

Regards

KT

 

SebSchoon1
Post Patron
Post Patron

Hello @jennratten 

 

Please find the code here

 

#"Index ajouté" = Table.AddIndexColumn(#"On ouvre tout", "Index", 1, 1),
#"On transforme l'index en texte" = Table.TransformColumnTypes(#"Index ajouté",{{"Name", type text}, {"Personnalisé.Item", type text}, {"Personnalisé.Data.Column1", type text}, {"Personnalisé.Data.Column2", type text}, {"Personnalisé.Data.Column3", type text}, {"Personnalisé.Data.Column4", type text}, {"Personnalisé.Data.Column5", type text}, {"Personnalisé.Data.Column6", type text}, {"Personnalisé.Data.Column7", type text}, {"Personnalisé.Data.Column8", type text}, {"Personnalisé.Data.Column9", type text}, {"Personnalisé.Data.Column10", type text}, {"Personnalisé.Data.Column11", type text}, {"Personnalisé.Data.Column12", type text}, {"Personnalisé.Data.Column13", type text}, {"Personnalisé.Data.Column14", type text}, {"Personnalisé.Data.Column15", type text}, {"Personnalisé.Data.Column16", type text}, {"Personnalisé.Data.Column17", type text}, {"Personnalisé.Data.Column18", type text}, {"Personnalisé.Data.Column19", type text}, {"Personnalisé.Data.Column20", type text}, {"Personnalisé.Data.Column21", type text}, {"Personnalisé.Data.Column22", type text}, {"Personnalisé.Data.Column23", type text}, {"Personnalisé.Data.Column24", type text}, {"Personnalisé.Data.Column25", type text}, {"Personnalisé.Data.Column26", type text}, {"Personnalisé.Data.Column27", type text}, {"Personnalisé.Data.Column28", type text}, {"Personnalisé.Data.Column29", type text}, {"Personnalisé.Data.Column30", type text}, {"Personnalisé.Data.Column31", type text}, {"Personnalisé.Data.Column32", type text}, {"Personnalisé.Data.Column33", type text}, {"Personnalisé.Data.Column34", type text}, {"Personnalisé.Data.Column35", type text}, {"Personnalisé.Data.Column36", type text}, {"Personnalisé.Data.Column37", type text}, {"Personnalisé.Data.Column38", type text}, {"Personnalisé.Data.Column39", type text}, {"Personnalisé.Data.Column40", type text}, {"Personnalisé.Data.Column41", type text}, {"Personnalisé.Data.Column42", type text}, {"Personnalisé.Data.Column43", type text}, {"Personnalisé.Data.Column44", type text}, {"Personnalisé.Data.Column45", type text}, {"Personnalisé.Data.Column46", type text}, {"Personnalisé.Data.Column47", type text}, {"Personnalisé.Data.Column48", type text}, {"Personnalisé.Data.Column49", type text}, {"Personnalisé.Data.Column50", type text}, {"Personnalisé.Data.Column51", type text}, {"Personnalisé.Data.Column52", type text}, {"Personnalisé.Data.Column53", type text}, {"Personnalisé.Data.Column54", type text}, {"Personnalisé.Data.Column55", type text}, {"Personnalisé.Data.Column56", type text}, {"Personnalisé.Data.Column57", type text}, {"Personnalisé.Data.Column58", type text}, {"Personnalisé.Data.Column59", type text}, {"Personnalisé.Data.Column60", type text}, {"Personnalisé.Data.Column61", type text}, {"Personnalisé.Data.Column62", type text}, {"Personnalisé.Data.Column63", type text}, {"Personnalisé.Data.Column64", type text}, {"Personnalisé.Data.Column65", type text}, {"Personnalisé.Data.Column66", type text}, {"Personnalisé.Data.Column67", type text}, {"Personnalisé.Data.Column68", type text}, {"Personnalisé.Data.Column69", type text}, {"Personnalisé.Data.Column70", type text}, {"Personnalisé.Data.Column71", type text}, {"Personnalisé.Data.Column72", type text}, {"Personnalisé.Data.Column73", type text}, {"Personnalisé.Data.Column74", type text}, {"Personnalisé.Data.Column75", type text}, {"Personnalisé.Data.Column76", type text}, {"Personnalisé.Data.Column77", type text}, {"Personnalisé.Data.Column78", type text}, {"Personnalisé.Data.Column79", type text}, {"Personnalisé.Data.Column80", type text}, {"Personnalisé.Data.Column81", type text}, {"Personnalisé.Data.Column82", type text}, {"Personnalisé.Data.Column83", type text}, {"Personnalisé.Data.Column84", type text}, {"Personnalisé.Data.Column85", type text}, {"Personnalisé.Data.Column86", type text}, {"Personnalisé.Data.Column87", type text}, {"Personnalisé.Data.Column88", type text}, {"Personnalisé.Data.Column89", type text}, {"Personnalisé.Data.Column90", type text}, {"Personnalisé.Data.Column91", type text}, {"Personnalisé.Data.Column92", type text}, {"Personnalisé.Data.Column93", type text}, {"Personnalisé.Data.Column94", type text}, {"Personnalisé.Data.Column95", type text}, {"Personnalisé.Data.Column96", type text}, {"Personnalisé.Data.Column97", type text}, {"Personnalisé.Data.Column98", type text}, {"Personnalisé.Data.Column99", type text}, {"Personnalisé.Data.Column100", type text}, {"Personnalisé.Data.Column101", type text}, {"Personnalisé.Data.Column102", type text}, {"Personnalisé.Data.Column103", type text}, {"Personnalisé.Data.Column104", type text}, {"Personnalisé.Data.Column105", type text}, {"Personnalisé.Data.Column106", type text}, {"Personnalisé.Data.Column107", type text}, {"Personnalisé.Data.Column108", type text}, {"Personnalisé.Data.Column109", type text}, {"Personnalisé.Data.Column110", type text}, {"Personnalisé.Data.Column111", type text}, {"Personnalisé.Data.Column112", type text}, {"Personnalisé.Data.Column113", type text}, {"Personnalisé.Data.Column114", type text}, {"Personnalisé.Data.Column115", type text}, {"Personnalisé.Data.Column116", type text}, {"Personnalisé.Data.Column117", type text}, {"Personnalisé.Data.Column118", type text}, {"Personnalisé.Data.Column119", type text}, {"Personnalisé.Data.Column120", type text}, {"Personnalisé.Data.Column121", type text}, {"Personnalisé.Data.Column122", type text}, {"Personnalisé.Data.Column123", type text}, {"Personnalisé.Data.Column124", type text}, {"Personnalisé.Data.Column125", type text}, {"Personnalisé.Data.Column126", type text}, {"Personnalisé.Data.Column127", type text}, {"Personnalisé.Data.Column128", type text}, {"Personnalisé.Data.Column129", type text}, {"Personnalisé.Data.Column130", type text}, {"Personnalisé.Data.Column131", type text}, {"Personnalisé.Data.Column132", type text}, {"Personnalisé.Data.Column133", type text}, {"Personnalisé.Data.Column134", type text}, {"Personnalisé.Data.Column135", type text}, {"Personnalisé.Data.Column136", type text}, {"Personnalisé.Data.Column137", type text}, {"Personnalisé.Data.Column138", type text}, {"Personnalisé.Data.Column139", type text}, {"Personnalisé.Data.Column140", type text}, {"Personnalisé.Data.Column141", type text}, {"Personnalisé.Data.Column142", type text}, {"Personnalisé.Data.Column143", type text}, {"Personnalisé.Data.Column144", type text}, {"Personnalisé.Data.Column145", type text}, {"Personnalisé.Data.Column146", type text}, {"Personnalisé.Data.Column147", type text}, {"Personnalisé.Data.Column148", type text}, {"Personnalisé.Data.Column149", type text}, {"Personnalisé.Data.Column150", type text}, {"Personnalisé.Data.Column151", type text}, {"Personnalisé.Data.Column152", type text}, {"Personnalisé.Data.Column153", type text}, {"Personnalisé.Data.Column154", type text}, {"Personnalisé.Data.Column155", type text}, {"Personnalisé.Data.Column156", type text}, {"Personnalisé.Data.Column157", type text}, {"Personnalisé.Data.Column158", type text}, {"Personnalisé.Data.Column159", type text}, {"Personnalisé.Data.Column160", type text}, {"Personnalisé.Data.Column161", type text}, {"Personnalisé.Data.Column162", type text}, {"Personnalisé.Data.Column163", type text}, {"Personnalisé.Data.Column164", type text}, {"Personnalisé.Data.Column165", type text}, {"Personnalisé.Data.Column166", type text}, {"Index", type text}}),
#"On cherche la colonne qui contient le mot Name" = Table.AddColumn(#"On transforme l'index en texte", "Check", each if Text.Contains([Personnalisé.Data.Column2], "Name") then "XORDERX - "&[Index] else if Text.Contains([Personnalisé.Data.Column3], "Name") then "ORDER - "&[Index] else if Text.Contains([Personnalisé.Data.Column3], "Name") then "ORDER - "&[Index] else null),
#"On transforme les erreurs en Null" = Table.ReplaceErrorValues(#"On cherche la colonne qui contient le mot Name", {{"Check", null}}),
#"On créé des Lots de commandes" = Table.FillDown(#"On transforme les erreurs en Null",{"Check","Check"}),
#"On regroupe le tout" = Table.Group(#"On créé des Lots de commandes", {"Check"}, {{"Etape1", each _, type table}}),
#"On enlève ce qui ne fait pas partie du lot" = Table.SelectRows(#"On regroupe le tout", each ([Check] <> null)),
#"On supprimme la colonne index (optimisation)" = Table.AddColumn(#"On enlève ce qui ne fait pas partie du lot", "Etape2", each Table.RemoveColumns([Etape1],"Index")),
#"On promeut les entêtes dans chaque lots" = Table.AddColumn(#"On supprimme la colonne index (optimisation)", "Col", each Table.PromoteHeaders( [Etape2], [PromoteAllScalars=true] )),
#"On ne garde que ce qui nous intéresse" = Table.RemoveColumns(#"On promeut les entêtes dans chaque lots",{"Check", "Etape1", "Etape2"}),

All the grouped orders are in The Step in bold and underlined.

 

I would like for each Table, to be able to reuse to column order.

 

🙂

 

In each table, some sizes could be the same or different

Here are examples of how you can dynamically set the column order.  If you can provide an example/screenshots of what the size column names are and the outcome you are trying to achieve I can provide a more specific solution. 

Match the column order for a different table:

Table.SelectColumns ( #"Name of Previous Step", Table.ColumnNames ( #"Other Table Name" ) )

Columns without "Size" in the name followed by columns with "Size" in the name:

Table.SelectColumns ( #"Name of Previous Step", let
   varBufferedList = List.Buffer ( Table.ColumnNames ( #"Name of Previous Step" ) ),
   varColumnsWithoutSize = List.Select ( varBufferedList, each not Text.Contains ( _, "Size" ) ),
   varColumnsWithSize = List.Select ( varBufferedList, each Text.Contains ( _, "Size" ) ),
   varFinalColumns = List.Combine ( {varColumnsWithoutSize, varColumnsWithSize} ) in
varFinalColumns ) 

 

 

Hello, many thanks for the tip!!

jennratten
Super User
Super User

Hello - yes, it is possible to dynamically reorder columns.  When you say 'keep columns that are the same in the same order', do you mean that the tables in your list do not all have the same columns - or do you mean you'd like columns that have the same values to be in the same order?  Are the size columns in the same table(s)?  Where are they getting out of order?  Can you share your script?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors