LOOKUPVALUE ERROR

[Anonymous]

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

[BA_Pete]

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

[Anonymous]

I can't use merge because in the first table there are double the lines of the second table

Are there other way?

My dax code doesn't work in powerbi too

[BA_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
    chgTypes

And 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
    chgTypes

Your 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
    pivotType

For this output:

Pete