Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Conditional Joining two Table

Hi ALl,   I have Two Table : Account No ,  Account Category   I want to connect these two table by Accoun Number   Ex: Account No : 11042-000 is want categorized Under Total Assets & Tatal Cash...
Need Help
  • richbenmintz's avatar
    richbenmintz
    6 years ago

    Hello @vengadeshpalani,

    The Measure I created provides the behavior you're seeing,

    see screenshot below, link to the sample pbix file shows pbix

    richbenmintz_0-1596631485276.png

SergioTorrinha's avatar
SergioTorrinha
Resolver II
6 years ago

Hi!
I belive you need some sort of "bridge" table mate. 🙂
Just:

1. In Power Query build a table with 5 fields: [Account No], [Account No Start From], [Account No Start To], [Key] = [Account No Start From] + [Account No Start To], where + represents string concatenation between 2 columns (you can also use SQL if your tables come from a sql data source)
2. build a [key] column in your Account category table

3. Connect the bridge table buit in 1 to the Account Category table by the [Key] field

4. Connect the bridge table buit in 1 to the Account No table by the [Account no] field
5. Use the fields from your bridge table to make the reports


Hope this helps 🙂