How to import code to Power query/Share code between queries

[TonyEssi]

Cross-posting this from Office365 Excel community as advised.

I have several tables that contain data collected in each of several years and a number of queries that generate a summary of three different columns in a data table for each year.

It seems possible to import code for a query from an external source so I was wondering if I could put the common code in a table and have the queries import the code dynamically.

My working code is below. Currently, I have copied the code into each individual query, changing the value of thisHeading and thisYear as required. I would like to minimise the work by importing all of the lines below these two statements.

let thisHeading="Activity", thisYear="2023", Source = Excel.CurrentWorkbook(){[Name="tblRegister" & thisYear]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source, { {"Committee", type text}, {"Activity", type text}, {"Funding Bucket", type text} }), #"Grouped Rows" = Table.Group(#"Changed Type", {thisHeading}, { {"Count", each Table.RowCount(_)} }), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{thisHeading, Order.Ascending}}), #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{thisHeading, thisHeading & "-" & thisYear}}) in #"Renamed Columns"

Doing some research, I came up with the following, but I can't get it to work. I receive the following error
"Expression.Error: [1,9-1,10] Token Eof expected.
Details:
[List]"

Is this actually possible?
If so, any help getting it to work would be appreciated.

Thanks in anticipation.

TonyS

let
thisHeading = "Activity",
thisYear = "2023",

// Retrieve the matching code from tblCodeRepository
CodeSource = Excel.CurrentWorkbook(){[Name="tblCodeRepository"]}[Content],

FilteredCode = Table.SelectRows(CodeSource, each [UniqueCodeTag] = "qryCrossCheckLists"),
RetrievedCode = FilteredCode{0}[Code],

// Combine the existing lines with the retrieved code
CombinedCode = Text.Combine({thisHeading, thisYear, RetrievedCode}, ", "),

// Evaluate the combined code to continue with the query
EvaluatedCode = Expression.Evaluate(CombinedCode, #shared)

in
EvaluatedCode

[Keezz]

Or approach this differently and simpler...
Start with Excel.Currentworkbook()

Filter all sheets or tables you don't need

Expand the "Content"column: Gets you all data of all sheets in one table

Do whatever analysis you want on the data

Group it by heading and year obviously

Rerun whenever you like.

If you are only intersted in the last year, for example, handle that in the first filter step. Sort and keep 1st row could be used here.

I could wirte the query for you, but I don't have enough to go on here.

[TonyEssi]

Thank you Keezz.

Each table is called "tblRegister" with a 4 digit suffix of indicative of the year. Each table is in a separate sheet (but that seems immaterial since the table names are unique). In the live workbook there are now four tables representing 2021, 2022, 2023, 2024. In my dev copy, for simplification, I only have 2023 & 2024 (which has minimal data).

Each table has a three columns that I am interested in, these being Activity, Committee & FundingBucket. A fourth column which I may use later, is an amount column.

Each row records the amount as well as string in each of the three columns indicating Activity, Committee and the FundingBucket.

Currently I just want to count the number of entries recorded for each categories (from which I later want to explore creating a chart and perhaps even showing the total amount recorded under each category.

More than anything else, I am using this task as an opportunity to learn what is possible.  Essentially I was exploring the possibility of reducing repetitive code. Your suggestion has me interested but my headspace isn't there to explore in this direction.   

If that gives you enough information to show me the code you suggest that would be great.

[PwerQueryKees · Accepted solution]

let
    // All content of the workbook
    Source = Excel.CurrentWorkbook(),
    // Make sure you fileter only the content you need
    #"Filtered Register Tables" = Table.SelectRows(Source, each Text.StartsWith([Name], "tblRegister")),
    // Isolate the year.
    // You can name the columns yourself here...
    #"Split Column by Character Transition" = Table.SplitColumn(#"Filtered Register Tables", "Name", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"TablePrefix", "Year"}),
    // End Result.
    // You can choose what columns to keep here
    // By refering to this query, you can build any analysis you want
    #"Expanded Content" = Table.ExpandTableColumn(#"Split Column by Character Transition", "Content", {"Activity", "Committee", "FundingBucket", "Amount"}, {"Activity", "Committee", "FundingBucket", "Amount"})
in
    #"Expanded Content"

In the worksheet:

[TonyEssi]

Thank you PwerQueryKees

I have just used your code and can see the benefit of combining everything together.  I will need to study this and how to use pivot tables on the combined data to get the summaries and charts I want to generate. 

[PwerQueryKees]

Go, go, go! Success! Feel free to ask....

[AlienSx]

Text.Combine({thisHeading, thisYear, RetrievedCode}, ", ")

this is not gonna work. I'd suggest you to make up RetrievedCode as a function with 2 arguments like this:

(x, y) => x + y

Then Expression.Evaluate(RetrievedCode, #shared) will generate a function for you. Next step - call that function in your code with thisHeading and thisYear as argument values. 

[TonyEssi]

Thanks AlienSx

It seems a useful way to approach this but I'm not quite sure how to implement what you are suggesting. I understand functions from a coding perspective but am very new to Power Query.

I tried creating twp parameters fp_thisHeading & fp_thisYear which rispectively create

"Activity" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

"2024" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

I'm not sure how to invoke the function and pass the parameters from the code I posted. I will have to study this later.