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

[Berean_50]

This is what I intend to do:

1. Given the following table:
   
2. I want to achieve the following result, by grouping the table by User Id, Name and Period Number, and transposing the rows of columns Category Status and  Category Requirement Met to columns:
   
3. The next step is to update the value of all the columns after Period Number according to the value Category Status and Category Requirements Met.  The challenge I'm facing is that these columns (eg. Category - Status) are created dynamically when the row is transposed. I can't refer to them by name. Instead the name should be Category Name + " - Status". 
   
   
4. Here's a step that I tried hard-coding the column name:
   = Table.ReplaceValue(#"Appended Query",null, each [Category Status], Replacer.ReplaceValue, {"Audit - Status"})
   This works if I hard-code the column name {"Audit - Status"}. 
   
   
5. My question is: how do I set the value of my dynamic columns by dynamically replacing the value of column name using the value of the Category Name column? Something like:
6. = Table.ReplaceValue(#"Appended Query",null, each [Category Status], Replacer.ReplaceValue, {[Category Name] & " - Status"}) // this doesn't work
7. I have attached the files I'm working with to make it easy.
   

   https://1drv.ms/x/s!AuHocvOERo6IhNl9pWhLKdaytFAvsQ?e=TYjrwv

   https://1drv.ms/u/s!AuHocvOERo6IhNl-PiqZfyLQVdAvyA?e=ysLiED

Perhaps the way I'm going about solving this issue is not the correct one. I'm open to suggestions. 

Thank you

[Thingsclump · Accepted solution]

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 

[Berean_50]

Thank you so much @Thingsclump