Forum Discussion
LOOKUPVALUE ERROR
Hi!
I am using powerbi and I have two different datas, a1 and a2, as in the tables below
| country | date | type | value |
| peru | 01/01/2023 | old | 5000 |
| peru | 02/01/2022 | old | 4000 |
| peru | 03/01/2022 | old | 8000 |
| peru | 04/01/2022 | old | 3000 |
| peru | 01/01/2023 | new | 1000 |
| peru | 02/01/2022 | new | 8000 |
| peru | 03/01/2022 | new | 500 |
| peru | 04/01/2022 | new | 9000 |
| USA | 01/01/2023 | old | 4000 |
| USA | 02/01/2022 | old | 6000 |
| USA | 03/01/2022 | old | 7000 |
| USA | 04/01/2022 | old | 2000 |
| USA | 01/01/2023 | new | 5000 |
| USA | 02/01/2022 | new | 8000 |
| USA | 03/01/2022 | new | 9000 |
| USA | 04/01/2022 | new | 4000 |
| country | date | value A |
| peru | 01/01/2023 | 4444 |
| peru | 02/01/2022 | 8000 |
| peru | 03/01/2022 | 9000 |
| peru | 04/01/2022 | 8000 |
| USA | 01/01/2023 | 5555 |
| USA | 02/01/2022 | 12000 |
| USA | 03/01/2022 | 6000 |
| USA | 04/01/2022 | 889 |
I tried add the column New using the code below, but it does not work
New = LOOKUPVALUE (a1[value],a1[date],a2[date]),a1[country],a2[country],a1[type],"new")
the following error message appears:
The name 'LOOKUPVALUE' wasn't recognized. Make sure it's spelled correctly.
How can I fix it?
the end result would be this
| country | date | value A | Old | New |
| peru | 01/01/2023 | 4444 | 5000 | 1000 |
| peru | 02/01/2022 | 8000 | 4000 | 8000 |
| peru | 03/01/2022 | 9000 | 8000 | 500 |
| peru | 04/01/2022 | 8000 | 3000 | 9000 |
| USA | 01/01/2023 | 5555 | 4000 | 5000 |
| USA | 02/01/2022 | 12000 | 6000 | 8000 |
| USA | 03/01/2022 | 6000 | 7000 | 9000 |
| USA | 04/01/2022 | 889 | 2000 | 4000 |
3 Replies
Hi Anonymous ,
The code you're trying is DAX, but I guess you're trying to do this in Power Query?
If so, then you should do a merge between the two tables, multi-selecting match fields in the merge dialog window like this:
a1[country] & a1[date] = a2[country] & a2[date]
Pete
Ok. Given table a1:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdDLCoAgEAXQf3EdNM7Ya9k3RCtxV7uoCKLfT7AXMybIgHDQO9datY7brjIFOvcHAclflmnwswAA5bKX4EXwIYYTEqTmxAhCnHyzzOPhp05lCUR8RIIUiShBNPcjfdfGWzFMyFJKJmQnFROyEkzkeHb5zcEKieSIbyv7CNu6Ew==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [country = _t, date = _t, #"type" = _t, value = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"date", type date}, {"value", Int64.Type}}) in chgTypesAnd table a2:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkgtKlXSUTIw1AciIwMjYyDHBAiUYnUQkkZQSSMgx8LAwABF0hhJ0hJd0gSbztBgR3QrTYEAWQ7ZRkMjNI3INpqhyaFYaGGpFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [country = _t, date = _t, value = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"date", type date}, {"value", Int64.Type}}) in chgTypesYour merged table would be as follows:
let Source = Table.NestedJoin(a2, {"country", "date"}, a1, {"country", "date"}, "a1", JoinKind.LeftOuter), expandNested = Table.ExpandTableColumn(Source, "a1", {"type", "value"}, {"type", "value.1"}), pivotType = Table.Pivot(expandNested, List.Distinct(expandNested[#"type"]), "type", "value.1", List.Sum) in pivotTypeFor this output:
Pete
