Forum Discussion

jaryszek's avatar
jaryszek
Super User
6 years ago

Replacing table with table

Hi Guys,

 

I am using PQ in Excel.

i have 2 sources: t_Defaults and t_Customizations with composite Key: Topology&Tier&Function

 

 

 

As a base i am using t_Defaults but what i want to do is:

1) If any row in t_Customizations is fullfilled (all columns, if one is empty - skip this row !) replace corresponding row (composite key) with corresponding composite key from t_Defaults. 

2) If table t_Customizations is empty - just use t_Defaults. 

 

Expected result:

 

So as you can see:

1) First row has been replaced from (Topo1&Tier1&Function1 key) t_Defaults with corresponding values from t_Customizations

2) Second row   has been replaced from (Topo2&Tier2&Function2 key) t_Defaults with corresponding values from t_Customizations

3) third row from t_defaults was not replaced because key Topo3&Tier3&Function3 do not exists in t_Customizations table. 

 

Can anybody help? I am starting my adventure with Power Query and DAX. 

Best,

Jacek

Need Help

4 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, jaryszek 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    t_Defaults:

     

    t_Customizations:

     

    You may go to 'Query Editor', create an index column for each table. Then you may create a blank query, input the following codes in 'Advanced Editor'.

    let
    Source = Table.NestedJoin(t_Customizations, {"Index"}, t_Defaults, {"Index"}, "t_Defaults", JoinKind.LeftOuter),
    Custom1 = Table.AddColumn(Source,"NewTopology",
each if
[Topology]=Table.Column([t_Defaults],"Topology"){0}and
[Tier]=Table.Column([t_Defaults],"Tier"){0}and
[Function]=Table.Column([t_Defaults],"Function"){0}
then [Topology]
else
Table.Column([t_Defaults],"Topology"){0}
),
    Custom2 = Table.AddColumn(Custom1,"NewTier",
each if
[Topology]=Table.Column([t_Defaults],"Topology"){0}and
[Tier]=Table.Column([t_Defaults],"Tier"){0}and
[Function]=Table.Column([t_Defaults],"Function"){0}
then [Tier]
else
Table.Column([t_Defaults],"Tier"){0}
),
    Custom3 = Table.AddColumn(Custom2,"NewFunction",
each if
[Topology]=Table.Column([t_Defaults],"Topology"){0}and
[Tier]=Table.Column([t_Defaults],"Tier"){0}and
[Function]=Table.Column([t_Defaults],"Function"){0}
then [Function]
else
Table.Column([t_Defaults],"Function"){0}
),
    Custom4 = Table.AddColumn(Custom3,"NewCol1",
each if
[Topology]=Table.Column([t_Defaults],"Topology"){0}and
[Tier]=Table.Column([t_Defaults],"Tier"){0}and
[Function]=Table.Column([t_Defaults],"Function"){0}
then [Col1]
else
Table.Column([t_Defaults],"Col1"){0}
),
    Custom5 = Table.AddColumn(Custom4,"NewCol2",
each if
[Topology]=Table.Column([t_Defaults],"Topology"){0}and
[Tier]=Table.Column([t_Defaults],"Tier"){0}and
[Function]=Table.Column([t_Defaults],"Function"){0}
then [Col2]
else
Table.Column([t_Defaults],"Col2"){0}
),
    Custom6 = Table.AddColumn(Custom5,"NewCol3",
each if
[Topology]=Table.Column([t_Defaults],"Topology"){0}and
[Tier]=Table.Column([t_Defaults],"Tier"){0}and
[Function]=Table.Column([t_Defaults],"Function"){0}
then [Col3]
else
Table.Column([t_Defaults],"Col3"){0}
),
    Custom7 = Table.AddColumn(Custom6,"NewCol4",
each if
[Topology]=Table.Column([t_Defaults],"Topology"){0}and
[Tier]=Table.Column([t_Defaults],"Tier"){0}and
[Function]=Table.Column([t_Defaults],"Function"){0}
then [Col4]
else
Table.Column([t_Defaults],"Col4"){0}
),
    Custom8 = Table.AddColumn(Custom7,"NewStorageProtocol",
each if
[Topology]=Table.Column([t_Defaults],"Topology"){0}and
[Tier]=Table.Column([t_Defaults],"Tier"){0}and
[Function]=Table.Column([t_Defaults],"Function"){0}
then [StorageProtocol]
else
Table.Column([t_Defaults],"StorageProtocol"){0}
),
    Custom9 = Table.AddColumn(Custom8,"NewProtocolVersion",
each if
[Topology]=Table.Column([t_Defaults],"Topology"){0}and
[Tier]=Table.Column([t_Defaults],"Tier"){0}and
[Function]=Table.Column([t_Defaults],"Function"){0}
then [ProtocolVersion]
else
Table.Column([t_Defaults],"ProtocolVersion"){0}
),
    #"Removed Other Columns" = Table.SelectColumns(Custom9,{"NewTopology", "NewTier", "NewFunction", "NewCol1", "NewCol2", "NewCol3", "NewCol4", "NewStorageProtocol", "NewProtocolVersion"})
in
    #"Removed Other Columns"

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • jaryszek's avatar
      jaryszek
      Super User
      6 years ago

      Thank you very much! This seems not to work for me, I added index column on each my table. 

       

      And implemented your formula into new query but i deleted or records from t_Customizations:

       

       
       

       

      So it should show all records. 

      Furthermore if i will add one new column i will have a lot of work to implement your solution. 

      We should find better way, i belive that exists. 

       

      Take a look from link here:

      https://stackoverflow.com/questions/36356471/   

      check-if-power-query-table-is-empty-then-return-something-else 

       

      If I could implement solution from here i could work around a lot of code but i am beginner and it is hard to understand how to do this. 

      Maybe you can help with it?

       

      It is just simple merging if row is or not based on index column...

      Jacek

      • jaryszek's avatar
        jaryszek
        Super User
        6 years ago

        i added new column to Defaults : Environments and i am proud because i think that i solved the issue:

         
         
        t_Customizations:
         
        and result is:
         
        seems to be easy in PQ:
         
        created 3 new queries:
        DefaultsSourceNotMatchCust:
        let
        Source = Excel.CurrentWorkbook(){[Name="t_Defaults"]}[Content],
        #"Merged Queries" = Table.NestedJoin(Source, {"Topology", "Tier", "Function"}, t_Customizations, {"Topology", "Tier", "Function"}, "t_Customizations", JoinKind.LeftAnti),
        #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"t_Customizations"})
        in
        #"Removed Columns"
         
        t_DefaultsMatchCust:
        let
        Source = Excel.CurrentWorkbook(){[Name="t_Defaults"]}[Content],
        #"Merged Queries" = Table.NestedJoin(Source, {"Topology", "Tier", "Function"}, t_Customizations, {"Topology", "Tier", "Function"}, "t_Customizations", JoinKind.Inner),
        #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Col1", "Col2", "Col3", "Col4", "StorageProtocol", "ProtocolVersion"}),
        #"Expanded t_Customizations" = Table.ExpandTableColumn(#"Removed Columns", "t_Customizations", {"Col1", "Col2", "Col3", "Col4", "StorageProtocol", "ProtocolVersion"}, {"Col1", "Col2", "Col3", "Col4", "StorageProtocol", "ProtocolVersion"})
        in
        #"Expanded t_Customizations"
         
        and after that i appened tables.
         
        Working like a charm!
        Best,
        Jacek