I have reached the limits of my current knowledge of PowerBI and I’m hoping someone may have some suggestions for me…
In Power Query, I’ve created a custom function to add a column in “Table 1” that points to “Table 2” (and other tables). Table 2 was created from a CSV file. While in Power Query, you can look at Table 1 and see pointer to Table 2 in the correct cell.
In “Table 3” I use another custom function to add a column to it. This function looks in Table 1 to find Table 2 and then looks up a value in Table 2, returning that value to the column in Table 3. While in Power Query, everything looks great and the field is populated correctly.
However, when I click “Close and Apply” to load the data, I am running into two problems. The main problem is that the column in Table 3 is not being filled in anymore. I also noticed that the column in Table 1 no longer has the table in it but is blank. I don’t really need that field once Table 3 is populated, but I suspect this field being blank (after “Close and Apply” is causing my Table 3 issue.
Working backwards, I used
SourceText = Text.Combine({"#""Lookup -", ThisMonth,""""}),
Source = try Expression.Evaluate(SourceText, #shared) otherwise ""
to populate the table column in Table 1. Would “Expression.Evaluate” work in Power Query but not after I close and apply to load the data? This would explain why the field is blank after I load (would take the “otherwise” path). If I take out the “try…. otherwise” I receive an error after close and apply, but no errors when I try to view errors (I’m suspecting because there are no errors when it runs in Power Query).
Please let me know if more details would be useful. Any thoughts or suggestions appreciated!
Thanks
