Forum Discussion
Errors using 'RELATED' with new data set
Hi mterry,
Please download the demo from the attachment.
I added a custom index in the [Location List] from the Query editor. Now only one value will be matched.
let code_p = [Code], year_p = [Year], index_p =[Index] in List.Accumulate(Table.SelectRows(#"Added Index", each [Code] = code_p and [Year] = year_p and [Index] <= index_p)[Office_Name], 0, (index, value) => index + 1)
Column = VAR strictName = LOOKUPVALUE ( 'Location List'[Office_Name], 'Location List'[Code], [Code], [Year], YEAR ( [Spend Date] ), [CustomIndex], 1 ) RETURN IF ( ISBLANK ( strictName ), "Misc", strictName )
Best Regards,
Thanks for the help; however, when I tried to create that column, I got an error saying "A table of multiple values was supplied where a single value was expected".
I do have multiple tables that have different spend information - is the easiest way to solve this problem to create a column on each of those? Or is there a way to have one single table (or even create that table in BI) where the 'strictName' is the default location?
Hi mterry,
Firstly, it's a column.
Secondly, can the [code] and the [year] determine an [Office_Name] in the location table? For example, no such situation:
| Tokyo | AB123 | 2018 |
| Kyoto | AB123 | 2018 |
Can you share your test file and some snapshots? Please mask the sensitive parts first.
Best Regards,
Edit: I said there weren't scenarios like the one you noted, however I went through the data line by line, and I did find instances where the same code referred to more than one name in the same year. I'm not sure if there are ways around this, for instance to only use the 'first' name?
Hi mterry,
Please download the demo from the attachment.
I added a custom index in the [Location List] from the Query editor. Now only one value will be matched.
let code_p = [Code], year_p = [Year], index_p =[Index] in List.Accumulate(Table.SelectRows(#"Added Index", each [Code] = code_p and [Year] = year_p and [Index] <= index_p)[Office_Name], 0, (index, value) => index + 1)
Column = VAR strictName = LOOKUPVALUE ( 'Location List'[Office_Name], 'Location List'[Code], [Code], [Year], YEAR ( [Spend Date] ), [CustomIndex], 1 ) RETURN IF ( ISBLANK ( strictName ), "Misc", strictName )
Best Regards,
Thank you so much for the help (and your time)!