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:
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.
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....