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 wan...
Need Help
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