Meging data across 3 tables

[Anonymous]

Hi,

I am beginner Power Query user, and I have what seems like should be easy thing to do, but haven't figured out yet.

I have created some unconnected queries to coallate my data, very simple tables, all 3 are Processor (name), and a number which represents how many pieces of work are completed for each person. I merged these in 2 steps, and end up with table like so:

Just need all 3 Processor columns combined, such that nobody's name is excluded. Also, each person should have 1 value in each number column (with null or 0 being an acceptable entry). The intended result of operations on above table would be like this:

Also, I don't understand why there is a "7" in the data with no processor, can't figure out where that came from. 😞 Please let me know what steps need to tell you about to get where I did. I didn't want to overexplain, but hoping enough here to get what I need. TIA!

Nyllix

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvRz93RV0lEyA2IY2wKJbWihFKsTrRTsGOQI4hkACRgbwTQyACsCspCROYZgsIdjpA9IgzGSlK+/j08k1DwQAsl4e/qGIglBJWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Processor = _t, #"Total STJ" = _t, Processor.1 = _t, #"Total Holds" = _t, Processor.2 = _t, #"Total States to be Worked" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Processor", type text}, {"Total STJ", Int64.Type}, {"Processor.1", type text}, {"Total Holds", Int64.Type}, {"Processor.2", type text}, {"Total States to be Worked", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.First(List.RemoveMatchingItems(Record.ToList(Record.SelectFields(_,{"Processor","Processor.1","Processor.2"})), {""}))),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Processor.1", "Processor.2", "Processor"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([Custom] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Custom", "Processor"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Processor", "Total STJ", "Total Holds", "Total States to be Worked"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Processor", type text}})
in
    #"Changed Type1"