Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
BA_Pete
Super User
2 years ago

 

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