Adding columns

[Dicken]

I wanted to a make table columns consistent  and came up  with re order , 
what was in the columns was irrelevaant, so ;

let
Source = #table ( type table [ One = Text.Type, Five = Text.Type,
Three = Text.Type ] ,
{{"a","b","b"}, {"b","c","d"} , {"d","a","c"}} ),
allcol = {"One","Two", "Three", "Four", "Five"},
Custom1 = Table.ReorderColumns( Source,
allcol ,MissingField.UseNull )
in
Custom1

can this be simplified   further or is there  a more effecient method ? 

[Poojara_D12]

Hi @Dicken 

Your Power Query (M) script for reordering and standardizing table columns is already quite efficient and idiomatic, especially given the use of Table.ReorderColumns with MissingField.UseNull to handle missing columns gracefully. This approach is beneficial when you want to enforce a consistent column schema across multiple tables, regardless of the input column order or presence.

In terms of simplification, this script is already minimal and readable. You define your expected schema (allcol), and then reorder the columns while automatically inserting any missing ones with nulls. There's no significant way to make it “more efficient” in performance or syntax without sacrificing clarity. One minor change could be directly embedding the allcol list inside the Table.ReorderColumns call to eliminate an extra step, like this:

let
    Source = #table(type table [One = Text.Type, Five = Text.Type, Three = Text.Type],
                    {{"a", "b", "b"}, {"b", "c", "d"}, {"d", "a", "c"}}),
    Custom = Table.ReorderColumns(Source, {"One", "Two", "Three", "Four", "Five"}, MissingField.UseNull)
in
    Custom

This avoids a variable assignment but functionally behaves the same. Otherwise, your current approach is robust and suitable for use in standardized Power Query workflows, especially when normalizing column layouts for appending or merging tables.

[slorin]

Hi @Dicken 

let
Source = #table ( type table [ One = Text.Type, Five = Text.Type,
Three = Text.Type ] ,
{{"a","b","b"}, {"b","c","d"} , {"d","a","c"}} ),
allcol = {"One","Two", "Three", "Four", "Five"},
Custom1 = #table(allcol, List.Repeat({},List.Count(allcol))) & Source
in
Custom1

Stéphane

[SundarRaj · Accepted solution]

Hi @Dicken,

Another interesting approach I could think of was this, thanks
Before Re-Ordering, to add more columns, two functions List.RemoveMatchingItems and List. Accumulate, here's how

Post this, follow up with the Re-ordering given below

1. Extracting ColNames and Loading it to Excel

2. Adding a Order Number Column in Excel as per the order you'd like

3. Load this new data back into Power Query (here, that is Col Order)
4. Last stepm using the Order Number col, arrange the data in ascending or descending order

5. Just use that query in the Table.ReorderColumns Function

[Dicken]

An interesting approach, thanks, for the suggestion, 

RD

[speedramps]

I want to help you but your description is too vague. Please write it again.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your buggy code that does not work and expect us to fathom what you want. (That is just crazy). ‌‌ 😀😀😀
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[Dicken]

Sorry but cannot make it any more simple, I gave all the code which can be pasted into a blank query, 
I want to add columns to a table  that are missing from a complete list of column names.