How to replace values of dynamic columns using values from another column in different rows

Hi Berean_50 

I am bit confused about row number 3 in step 1 of yours. This where you have ID as 100 but name as Mary. But other than below code seems to work. I will wait for your feedback.

Input:

Result: (i could not take screenshot with all columns)

Code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJR8srPyANShkDsWJqSWQKkA1LzUjLz0oEsv3ylWB0sKl1TSpMTSzLzQXzn/NyCnNSS1BQgOzK1GKzBCKzBN7GoEkgZ4TMaQyWQmZ1aAlGDYXQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Name = _t, #"Period Number" = _t, #"Category Name" = _t, #"Category Status" = _t, #"Category Requiment Met" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Name", type text}, {"Period Number", Int64.Type}, {"Category Name", type text}, {"Category Status", type text}, {"Category Requiment Met", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Category name with status", each [Category Name] & " - Status"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Category Name] & " - Requirements met"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Category Name"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Category name with status"]), "Category name with status", "Category Status"),
#"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Custom]), "Custom", "Category Requiment Met")
in
#"Pivoted Column1"

accpet it as solution if it works for you 

Thanks

thingsclump

www.thingsclump.com