Forum Discussion
How do I make my power query automation more dynamic?
I have created an automation using power query where it'll fetch data from a workbook called "Data". I have manipulated the data to achieve my desired table format. The table's final columns are: Vendor Name, Contract #, and (after pivoting the columns) the Total Amount Outstanding for the years 2019-2024 (6 separate columns). The problem I am running into is that some of my input, "Data", workbooks only have years 2019-2023 while some have years 2020-2024. Since the initial data file I constructed my table around contained the years 2019-2024, any file I load into the table that does not include the same years reports an error message saying something like: "The column 2024 of the table was not found". I know these sorts of things work great with standardized datasets but in this case, my years will fluctuate by one or two. How can I make my table more dynamic so that regardless of whether the file contains the same years (2019-2024) or different (i.e., 2019-2023, 2020-2024, etc...) it will still read it in and perform my desired formatting without any error messages?
1.Use Power Query to cleanse your data, combine data, format data.
2. Normalize your data
Compartmentalize your transaction table from your lookup tables
Create a calendar table
3. Create a data model relationships to your transaction table to your lookup tables
4. Create a Pivot table using power pivot
5. Within the data model editor is where you can create your measures
- Did you use "Choose Columns" button? the M code generated by that button is just rubbish. Bettor paste the code here if you want it cleaned.
