Replacing table with table

[jaryszek]

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

[v-alq-msft]

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]

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]

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

[jaryszek]

I want to add example file but i am not seeing option on this forum so i am sharing google drive link:

https://drive.google.com/file/d/1XhjPvYp8QANPFb1n-KJG5aMSnPz4SiZC/view?usp=sharing