Forum Discussion
How to import code to Power query/Share code between queries
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:
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.
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:
- 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.Go, go, go! Success! Feel free to ask....