Forum Discussion

FelipMark's avatar
FelipMark
Helper II
3 years ago
Solved

how to append query without grabbing existing data

Hello everyone, I want to use the function to add queries, but without adding data that already correspond to the ID column so as not to duplicate information. Example: Table 1: DEAL_ID ACC...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    3 years ago

    Use this code for Table1

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTIyMjYGUoYGBiAOEJsqxepEK5mamVsgZE3hsmZgWUsDQxDH2NgEZIQZWNYQojcWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DEAL_ID = _t, ACCOUNT_ID = _t, VALUE = _t, ORIGIN_ID = _t, PIPELINE_ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DEAL_ID", Int64.Type}, {"ACCOUNT_ID", Int64.Type}, {"VALUE", Int64.Type}, {"ORIGIN_ID", Int64.Type}, {"PIPELINE_ID", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"DEAL_ID"}, Table2, {"DEAL_ID"}, "Table2", JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"DEAL_ID", "CAMPAING_ID", "VALUE", "ADSET_ID"}, {"DEAL_ID.1", "CAMPAING_ID", "VALUE.1", "ADSET_ID"}),
    Custom1 = Table.ReplaceValue(#"Expanded Table2", each [DEAL_ID], each [DEAL_ID]??[DEAL_ID.1], Replacer.ReplaceValue, {"DEAL_ID"}),
    Custom2 = Table.ReplaceValue(Custom1, each [VALUE], each [VALUE]??[VALUE.1], Replacer.ReplaceValue, {"VALUE"}),
    #"Removed Columns" = Table.RemoveColumns(Custom2,{"DEAL_ID.1", "VALUE.1"})
in
    #"Removed Columns"
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
3 years ago

Use this code for Table1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTIyMjYGUoYGBiAOEJsqxepEK5mamVsgZE3hsmZgWUsDQxDH2NgEZIQZWNYQojcWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DEAL_ID = _t, ACCOUNT_ID = _t, VALUE = _t, ORIGIN_ID = _t, PIPELINE_ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DEAL_ID", Int64.Type}, {"ACCOUNT_ID", Int64.Type}, {"VALUE", Int64.Type}, {"ORIGIN_ID", Int64.Type}, {"PIPELINE_ID", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"DEAL_ID"}, Table2, {"DEAL_ID"}, "Table2", JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"DEAL_ID", "CAMPAING_ID", "VALUE", "ADSET_ID"}, {"DEAL_ID.1", "CAMPAING_ID", "VALUE.1", "ADSET_ID"}),
    Custom1 = Table.ReplaceValue(#"Expanded Table2", each [DEAL_ID], each [DEAL_ID]??[DEAL_ID.1], Replacer.ReplaceValue, {"DEAL_ID"}),
    Custom2 = Table.ReplaceValue(Custom1, each [VALUE], each [VALUE]??[VALUE.1], Replacer.ReplaceValue, {"VALUE"}),
    #"Removed Columns" = Table.RemoveColumns(Custom2,{"DEAL_ID.1", "VALUE.1"})
in
    #"Removed Columns"