Update table, unclear join

[JackDanIce]

Hi,

I have a master table and an input table loaded into PQ. Both tables contain a primary key and I wish to update all values from the input table into/replace those in the master table.

I've been trying merge queries with Left Inner Joins and Left Anti Joins (I have a basic idea of the differences) but after I expand the table, I'm still struggling to 'replace'/write over the original values

Can anyone explain or suggest how to merge two tables like this? For now I just need to replace values against primary key.

TIA,
Jack

 

[alansidman]

How about some sample data? Use XL2BB to show us.

 

[JackDanIce]

I would but I'm working from a client's PC which is restricted and unable to send data to personal PC to redact and recreate.

If this makes sense:

Start
Table Master: 3 columns, Index(1 to 10), Value1(all 0), Value2(all 0)

User
Table Input: 3 columns, Index (selected value e.g. 3), Value1(user enters: 3), Value2(user enters: 4)

How do I get Table Master to update Index(3) with Value1(3) and Value2(4) from Table Input?

Both tables exist in PQ, Table Master then outputs to a datasheet which I pivot into a dashboard.


(I will try to mock something up on personal PC and add back later if this isn't clear)

 

[alansidman]

Jack,
I have set up the tables as you directed. Next, based upon that data, what should your expected results look like. I am not sure if this should be a parameter query or just a simple join. Without seeing your expected results, it is difficult to determine what exactly you want.

Alan

 

[JackDanIce]

Thanks Alan, does this help?

						Select Index
						1
												Updated/Refreshed
Data Table						User Input Table						Data Table
Index	Value1	Value2				Index	Value1	Value2				Index	Value1	Value2
1	0	0				1	jack	daniels				1	jack	daniels
2	0	0										2	on	ice
3	0	0				Repeating User Action						3	0	0
						User makes multiple updates
Initial						for each index value						Table output
						over time
												Output table needs to show
						User Input Table						latest values for each Index
						Index	Value1	Value2
						2	on	ice

User selects an index and then updates values for it. These values need to appear in an output table, replacing any previous values (or null)

My set up is an index table, the input table and then I'm trying to join them together to create the output table i.e. where the number of records in the input table and output table are the same.

 

[JackDanIce]

Index table is connection only
The other two are connected to tables

Found a work-around of sorts, the M code for output table:

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Pillars"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Type", "Data Type", "Min Value", "Max Value", "Calculation"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Pillar ID", Order.Ascending}}),      
    #"Promoted Headers" = Table.PromoteHeaders(Table.Transpose(Table.ReorderColumns(#"Sorted Rows",{"Pillar Label Name", "Pillar ID"})), [PromoteAllScalars=true]),
    #"Merged Queries" = Table.NestedJoin(#"Promoted Headers", {"Overall Score"}, tbl_Data, {"Fund Name"}, "tbl_Data", JoinKind.RightAnti),
    #"Expanded tbl_Data" = Table.ExpandTableColumn(#"Merged Queries", "tbl_Data", {"Fund Name", "Fund Name and Class", "ISIN"}),
    #"Reorder Columns" = Table.ReorderColumns(#"Expanded tbl_Data", List.LastN(Table.ColumnNames(#"Expanded tbl_Data"), 3) & List.FirstN(Table.ColumnNames(#"Expanded tbl_Data"), List.Count(Table.ColumnNames(#"Expanded tbl_Data"))-3)),
    #"Merged Queries1" = Table.NestedJoin(#"Reorder Columns", {"ISIN"}, tbl_Input_Save, {"ISIN"}, "tbl_Input_Save", JoinKind.LeftAnti),
    #"Sorted Rows1" = Table.Sort(#"Merged Queries1",{{"Fund Name and Class", Order.Ascending}}),  
    #"Appended Query" = Table.Combine({Table.RemoveColumns(#"Sorted Rows1",{"tbl_Input_Save"}), tbl_Input_Save}),  
    #"Tidy Up" = Table.Sort(Table.ReplaceValue(#"Appended Query",null, 0, Replacer.ReplaceValue, List.LastN(Table.ColumnNames(#"Appended Query"), List.Count(Table.ColumnNames(#"Appended Query"))-3)), {{"Fund Name and Class", Order.Ascending}})
in
    #"Tidy Up"

I can see in the preview on this line:

#"Appended Query" = Table.Combine({Table.RemoveColumns(#"Sorted Rows1",{"tbl_Input_Save"}), tbl_Input_Save}),

It's not showing the same values as tbl_Input_Save which has got values in it

I'm using Refresh-All to update all the tables

 

[alansidman]

Sorry Jack. I am not going to be able to help on this issue. You have confused me thoroughly as the relationship between the last two posts are not consistent.

 

[JackDanIce]

Ignore the last post then, if that helps, just the flow of data from the initial table, user makes updates, to the output table.
Index 1 starts with values of 0 and 0
The user adds values "jack" and "daniels" for index 1
The output table shows Index 1 with values "Jack" and "daniels"
The User then updates for Index 2
The output table shows these updates for Index 2 (and previous values for index 1 as these haven't changed)

 

[alansidman]

I've played with this and can't seem to get what you are looking for. Hopefully, someone with more Mcode skills will jump in.

 

[JackDanIce]

Thanks for trying Alan, do appreciate it. Spoke to a database friend last night - I need some kind of "Commit" command, in SQL it's easy to update records by key and commit changes (apparently)