Forum Discussion
Solved
Flag Line items that meet lookup criteria
Hoping someone can assist me here in a solving this problem I have in Power Query. What I would like to do is flag (Yes or No) the line items in an order where a primary id matches and also its cor...
Hi jjkrmr ,
one possible approach is this:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJLbsMwDATQu3idAvxJJJdNjC57gSD3v0YlOqWsRNkRDzIwQ/N+3763y4YowNKH7XF5EjWiNtAgBVZrA5/Jvb+SEyEZtqGciaW0oSZRATZvgyY5MmH/0IKu/RU6eoEMNhstjNMEqmrNaBOVQdWMPKMFGSqNaP8UyxjRCqgo99hJDoKuPSyE3XoyJlIfDWajV7t+AWeL2SWrnPzneF8+eH3138P1g1t6LsEniiU86+0xwnElOKgWEM5y+3El6jV7BZkIjr8zUUkiBjHKIvtxOCiWHYIq1rhLeyd/p1N80nb4ER8XRgvjhY0KTuQWF1AW1ko8/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, ID = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source, {{"Order", type text}, {"ID", type text}, {"Value", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, LookupTable, {"ID"}, "LookupTable", JoinKind.Inner), #"Expanded LookupTable" = Table.ExpandTableColumn(#"Merged Queries", "LookupTable", {"ID", "Alt ID"}, {"ID.1", "Alt ID"}), #"Merged Queries1" = Table.NestedJoin( #"Expanded LookupTable", {"Alt ID"}, #"Changed Type", {"ID"}, "Expanded LookupTable", JoinKind.Inner ), #"Expanded Expanded LookupTable" = Table.ExpandTableColumn( #"Merged Queries1", "Expanded LookupTable", {"ID", "Value"}, {"ID.2", "Value.1"} ), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Expanded LookupTable", {"Order", "ID", "Value", "Alt ID", "Value.1"}), #"Added Custom" = Table.AddColumn( #"Removed Other Columns", "Custom", each #table({"ID", "Value"}, {{[ID], [Value]}, {[Alt ID], [Value.1]}}) ), #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom", {"Order", "Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"ID", "Value"}, {"ID", "Value"}), #"Merged Queries2" = Table.NestedJoin( #"Changed Type", {"Order", "Value"}, #"Expanded Custom", {"Order", "Value"}, "Expanded Custom", JoinKind.LeftOuter ), #"Expanded Expanded Custom" = Table.ExpandTableColumn(#"Merged Queries2", "Expanded Custom", {"ID"}, {"Flag"}) in #"Expanded Expanded Custom"Not the most elegant, but working...
Please also check the file enclosed.
ImkeF
Community Champion
Community ChampionHi jjkrmr ,
one possible approach is this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJLbsMwDATQu3idAvxJJJdNjC57gSD3v0YlOqWsRNkRDzIwQ/N+3763y4YowNKH7XF5EjWiNtAgBVZrA5/Jvb+SEyEZtqGciaW0oSZRATZvgyY5MmH/0IKu/RU6eoEMNhstjNMEqmrNaBOVQdWMPKMFGSqNaP8UyxjRCqgo99hJDoKuPSyE3XoyJlIfDWajV7t+AWeL2SWrnPzneF8+eH3138P1g1t6LsEniiU86+0xwnElOKgWEM5y+3El6jV7BZkIjr8zUUkiBjHKIvtxOCiWHYIq1rhLeyd/p1N80nb4ER8XRgvjhY0KTuQWF1AW1ko8/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, ID = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Order", type text}, {"ID", type text}, {"Value", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, LookupTable, {"ID"}, "LookupTable", JoinKind.Inner),
#"Expanded LookupTable" = Table.ExpandTableColumn(#"Merged Queries", "LookupTable", {"ID", "Alt ID"}, {"ID.1", "Alt ID"}),
#"Merged Queries1" = Table.NestedJoin(
#"Expanded LookupTable",
{"Alt ID"},
#"Changed Type",
{"ID"},
"Expanded LookupTable",
JoinKind.Inner
),
#"Expanded Expanded LookupTable" = Table.ExpandTableColumn(
#"Merged Queries1",
"Expanded LookupTable",
{"ID", "Value"},
{"ID.2", "Value.1"}
),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Expanded LookupTable", {"Order", "ID", "Value", "Alt ID", "Value.1"}),
#"Added Custom" = Table.AddColumn(
#"Removed Other Columns",
"Custom",
each #table({"ID", "Value"}, {{[ID], [Value]}, {[Alt ID], [Value.1]}})
),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom", {"Order", "Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"ID", "Value"}, {"ID", "Value"}),
#"Merged Queries2" = Table.NestedJoin(
#"Changed Type",
{"Order", "Value"},
#"Expanded Custom",
{"Order", "Value"},
"Expanded Custom",
JoinKind.LeftOuter
),
#"Expanded Expanded Custom" = Table.ExpandTableColumn(#"Merged Queries2", "Expanded Custom", {"ID"}, {"Flag"})
in
#"Expanded Expanded Custom"Not the most elegant, but working...
Please also check the file enclosed.
Sorry for the inconvenience, but if it is added to the LookupTable for example: ID=171281 Alt ID=124032, there will be duplicate lines, would there be a way to fix it?
- dufoq3
Hi, like this?
let OrderTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJLbsMwDATQu3idAvxJJJdNjC57gSD3v0YlOqWsRNkRDzIwQ/N+3763y4YowNKH7XF5EjWiNtAgBVZrA5/Jvb+SEyEZtqGciaW0oSZRATZvgyY5MmH/0IKu/RU6eoEMNhstjNMEqmrNaBOVQdWMPKMFGSqNaP8UyxjRCqgo99hJDoKuPSyE3XoyJlIfDWajV7t+AWeL2SWrnPzneF8+eH3138P1g1t6LsEniiU86+0xwnElOKgWEM5y+3El6jV7BZkIjr8zUUkiBjHKIvtxOCiWHYIq1rhLeyd/p1N80nb4ER8XRgvjhY0KTuQWF1AW1ko8/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, ID = _t, Value = _t]), LookupTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fctBDoAwCETRu7CuCQwgdOs1mt7/Gq01xujCHfN+aI0kBClU1hFBvbxNzT8GY8UyKBCVj411hmfZVZ016xmcIXKbWP7YPn/7AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Alt ID" = _t]), MergedQueries1 = Table.NestedJoin(OrderTable, {"ID"}, LookupTable, {"ID"}, "LookupTableID", JoinKind.LeftOuter), MergedQueries2 = Table.NestedJoin(MergedQueries1, {"ID"}, LookupTable, {"Alt ID"}, "LookupTableAltID", JoinKind.LeftOuter), Ad_FlagID = Table.AddColumn(MergedQueries2, "FlagID", each ([LookupTableID] & [LookupTableAltID])[ID]{0}?), RemovedColumns = Table.RemoveColumns(Ad_FlagID,{"LookupTableID", "LookupTableAltID"}) in RemovedColumnsThank you very much for your patience.
