Forum Discussion

adentler's avatar
adentler
Advocate I
11 months ago
Solved

Power Query Add Custom Column with multiple criteria lookup from other table

  Hello, I am trying to use Power Query to add a column to a table during the transform process. I need to lookup multiple values from Table 1 (Finance Book) in Table 2 (AC_CC Mapping), and return t...
Need Help
  • adentler's avatar
    adentler
    11 months ago

    All - I believe I was able to determine the solution thanks to all of your inputs. I also found the cyclical reference. It was a calculated column post-transform on the Finance Book table.

     

    Ultimately, I was able to use the following code and the column was added as needed:

     

    = Table.AddColumn(#"Changed Type", "Contract Year", (F) => 
  Table.SelectRows( 
    #"ACC_CC Mapping", 
    (C) => C[Start Date] <= F[posting_date] and C[End Date] >= F[posting_date] 
  )
)

     

     

v-kpoloju-msft's avatar
v-kpoloju-msft
Community Support
11 months ago

Hi adentler,

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to SundarRaj, MarkLaf, AmiraBedh, for those inputs on this thread.

Has your issue been resolved? If the response provided by the community member SundarRaj, MarkLaf, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

adentler's avatar
adentler
Advocate I
11 months ago

I am still working through the solutions provided and am happy to update this post once I have. I sencerely appreciate the time and effort @SundarRaj, @MarkLaf, @AmiraBedh, have provided to help me resolve me issue. 

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    11 months ago

    Hi adentler,

    Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

    Thank you.

    • adentler's avatar
      adentler
      Advocate I
      11 months ago

      This has been resolved. Thank you! Everyones suggestions and solutions did help. I continued to run into the cyclical value but when I ultimately resolved it, updated my SQL query, and managed my relationships better.. I overcame the issue.