Forum Discussion
Mutiple classified/Trusted data error when appending two tables
Hi All.
I've run into an error when I append two queries/tables in PowerQuery:
It's worth noting the two tables/queries I'm appending have a custom function. I'm using the function so the query can iterate through a list of excel paths and sheets.
Query 1:
let
Source = Excel.Workbook(File.Contents("C:\Users\Sabastian.Chetty\Desktop\Paths - M code - Finance Reports.xlsx"), null, true),
fnSMU_Sheet = Source{[Item="fnSMU",Kind="Sheet"]}[Data],
//Cleaning Starts Here:
PromotedHeaders = Table.PromoteHeaders(fnSMU_Sheet, [PromoteAllScalars=true]),
InvokedCustomFunction = Table.AddColumn(PromotedHeaders, "SMU_Excel_File_Cleaner", each SMU_Excel_File_Cleaner([Paths], [Sheet Name])),
ExpandedSMU = Table.ExpandTableColumn(InvokedCustomFunction, "SMU_Excel_File_Cleaner", {"Parent Category", "Cost Category", "Month", "Value", "Department", "Management Unit"}, {"SMU_Excel_File_Cleaner.Parent Category", "SMU_Excel_File_Cleaner.Cost Category", "SMU_Excel_File_Cleaner.Month", "SMU_Excel_File_Cleaner.Value", "SMU_Excel_File_Cleaner.Department", "SMU_Excel_File_Cleaner.Management Unit"})
in
ExpandedSMU
Query 2:
let
Source = Excel.Workbook(File.Contents("C:\Users\Sabastian.Chetty\Desktop\Paths - M code - Finance Reports.xlsx"), null, true),
fnAMU_Sheet = Source{[Item="fnAMU",Kind="Sheet"]}[Data],
// Cleaning Starts Here:
PromotedHeaders = Table.PromoteHeaders(fnAMU_Sheet, [PromoteAllScalars=true]),
InvokedCustomFunction = Table.AddColumn(PromotedHeaders, "AMU_Excel_File_Cleaner", each AMU_Excel_File_Cleaner([Paths], [Sheet Name])),
ExpandedAMU = Table.ExpandTableColumn(InvokedCustomFunction, "AMU_Excel_File_Cleaner", {"Parent Category", "Cost Category", "Month", "Value", "Department", "Management Unit"}, {"AMU_Excel_File_Cleaner.Parent Category", "AMU_Excel_File_Cleaner.Cost Category", "AMU_Excel_File_Cleaner.Month", "AMU_Excel_File_Cleaner.Value", "AMU_Excel_File_Cleaner.Department", "AMU_Excel_File_Cleaner.Management Unit"})
in
ExpandedAMU
After running I am able to append these into a single table, I used the UI and get the following code:
let
Source = Table.Combine({fnSMU, fnAMU})
in
Source
After all of these steps I get the error listed at the start of this post.
I did some digging and found that two sources may be causing the issue so I followed the steps in the following article with no luck: https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
Any idea where I'm going wrong?
- Anonymous4 years ago
Hi Sabo256w ,
Please try the solutions provided in this blog:
- The first method to fix this problem is to avoid the fnGetParameter function all together and just hard code the file paths. While this works, you cut all dynamic capability from the query that you went to the effort of implementing. In my opinion, this option is awful.
- If you want to preserve the dynamic nature of the fnGetParameter function, the only way to fix this error today is to perform the steps below in this EXACT order!
- Turn on Fast Combine (Power Query –> Options –> Privacy –> Ignore Privacy Levels)
- Save the workbook
- Close Excel
- Restart Excel
- Refresh the query
Here is a similar thread:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- AnonymousNot applicable
Hi Sabo256w ,
Please try the solutions provided in this blog:
- The first method to fix this problem is to avoid the fnGetParameter function all together and just hard code the file paths. While this works, you cut all dynamic capability from the query that you went to the effort of implementing. In my opinion, this option is awful.
- If you want to preserve the dynamic nature of the fnGetParameter function, the only way to fix this error today is to perform the steps below in this EXACT order!
- Turn on Fast Combine (Power Query –> Options –> Privacy –> Ignore Privacy Levels)
- Save the workbook
- Close Excel
- Restart Excel
- Refresh the query
Here is a similar thread:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- FerdieBothaFrequent Visitor
Brilliant!!!!
- Sabo256wFrequent Visitor
Thanks Eyelyn!
Works perfect 😀