How do I change the M code to ensure any new columns added each month will get included?

[michellerob2000]

Each approver column is hard coded, so how can I change the m code to ensure if a new column is added I wont have to keep amending the code ie "Approver 12"

I saw somewhere about table.combine which removes all the hard coded ive added. 

Im not sure about it

Thank you
Michelle.

[AlienSx]

Hello, @michellerob2000 you need to read a list of column names, modify them (add suffix .2) and use in expand table column step.

approvers = List.Select(Table.ColumnNames(Source[Table3]{0}), each Text.StartsWith(_, "Approver")), 
mod_approvers = List.Transform(approvers, each _ & ".2"),
expand = Table.ExpandTableColumn(Source, "Table3", {"App Group name"} & approvers, {"App Group name"} & mod_approvers)

[michellerob2000]

How do I change my coding to yours: adding in the Text.StartsWith

= Table.ExpandTableColumn(Source, "Table3", {"App Group name", "Approver 1", "Approver 2", "Approver 3", "Approver 4", "Approver 5", "Approver 6", "Approver 7"}, {"App Group name", "Approver 1", "Approver 2", "Approver 3", "Approver 4", "Approver 5", "Approver 6", "Approver 7"})

Do i remove all and then add it like this:
= Table.ExpandTableColumn(Source, "Table3", {"App Group name"] Text.StartWith( "Approver")

[AlienSx]

@michellerob2000 simply replace your step (with approvers hardcoded) with my code. No need to modify anything, just replace your step with mine (3 steps).

[123abc]

It seems like you want to dynamically incorporate new columns in your M code without hardcoding each column's name. To achieve this, you can follow these steps:

1. Transform your data using Power Query: Power Query (M) in tools like Microsoft Excel or Power BI provides a flexible way to transform and manipulate your data. If you're working with data in Excel, you can load your data into Power Query by going to the "Data" tab and selecting "Get Data" or "From Table/Range." If you're using Power BI, you can perform these transformations directly in the Power Query Editor.

2. Remove the hardcoding: In your M code, replace any references to specific column names (e.g., "Approver 12") with more dynamic code that can handle any new columns. For example, if you have multiple "Approver" columns, you can use wildcard matching to select columns with names that match a pattern like "Approver*" or "Approver #" where "#" is a placeholder for any number.

   Here's an example of how you can do this:

let
Source = ... , // Your data source
// Use wildcard matching to select columns with names that match the pattern "Approver*"
ApproverColumns = Table.SelectColumns(Source, each Text.StartsWith(ColumnName, "Approver")),
// Other transformations as needed
in
ApproverColumns

1. This code will select all columns that start with "Approver" and include them in your query without explicitly specifying each column.

2. Consider using functions: If your data structure involves a dynamic number of columns or you expect significant changes over time, consider creating custom M functions to handle the transformation of these columns. This way, you can apply the function to your data each time you need to refresh it, and the function can adapt to the changing column names.

3. Automate the refresh process: To ensure that any new columns added each month are included, you can automate the data refresh process in Excel or Power BI. This way, your query will update with the latest data automatically without requiring manual adjustments to the M code.

By following these steps, you can create a more flexible and dynamic data transformation process that adapts to changes in your data structure, such as the addition of new columns.

[michellerob2000]

Thank you for that, but, do I do it at the Expandedtable3 step? and change the coding below to just Approver*?

= Table.ExpandTableColumn(Source, "Table3", {"App Group name", "Approver 1", "Approver 2", "Approver 3", "Approver 4", "Approver 5", "Approver 6", "Approver 7", "Approver 8", "Approver 9", "Approver 10", "Approver 11"}, {"App Group name", "Approver 1.2", "Approver 2.2", "Approver 3.2", "Approver 4.2", "Approver 5.2", "Approver 6.2", "Approver 7.2", "Approver 8.2", "Approver 9.2", "Approver 10.2", "Approver 11.2"})

Thanks

[123abc]

Yes, you can modify the code at the ExpandedTable3 step to make it more dynamic. Instead of explicitly listing all the columns "Approver 1," "Approver 2," and so on, you can use a wildcard pattern to expand all columns that match the pattern "Approver *." Here's how you can modify the code:

= Table.ExpandTableColumn(Source, "Table3", {"App Group name", "Approver *"})

This change will expand all columns in "Table3" that match the pattern "Approver ," where "" acts as a wildcard to match any column name starting with "Approver." This way, you won't have to specify each individual column number, making your code more adaptable to changes in the number of approver columns.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[michellerob2000]

Thank you but it didnt work:

let
Source = Table.NestedJoin(Table1, {"App <1k (REQs ONLY) - ROLE/GROUP"}, Table3, {"App Group name"}, "Table3", JoinKind.LeftOuter),
#"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"App Group name", "Approver *"}, {"App Group name", "Approver *"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Table3",{{"App Group name", "Level 1 Group"}})
in
#"Renamed Columns"