Replace values in matrix/table based on reference table

[Mants]

Hi all, 

I'm trying to create a table with a specific strcuture and im not sure what the best way is to create it, i hope someone could help.

My situation is that i have a table with codes, and a table with a certain amount, per code. i need to recreate the table with the same Areas and column headers, and replace the codes in the cells with the corresponding values (from the Sums table), and where there is no value, put 0 (or null)

Eventually there will be a table with around 1000 separate cells (and separate codes from table 1)

Is this possible to do in power query and if so what options are there? 

Thanks

[wdx223_Daniel]

if the combined value of Area and Cluster in Table2 is unique.

=Table.ReplaceValue(Table1,each [Area],"",(x,y,z)=>Table2{[Area=y,Cluster=x]}?[Count]? ??x,List.Skip(Table.ColumnNames(Table1)))

[Omid_Motamedise · Accepted solution]

Just copy the next code and past it into advance editor.

Summery:
Step 1: Unpviot table 1

Step 2: Merge the Table 2 and result of unpviot table 1

Step 3: remove extra column

Step 4: Pivot the result table

let

     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixKTVRwVNJRMgRiIyA2VorVgQo7AbmmQGwGxOYIYWcg1wKILUG6DJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Area = _t, #"round 1" = _t, #"round 2" = _t, #"round 3" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Area"}, "Attribute", "Value"),
    Source2= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixKTVRwVNJRMgJiQ3OlWB2omBOQbwoSN0QVMwepM0GIOQP5lkBsbKYUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Area = _t, Cluster = _t, Count = _t]),
    #"Merged Queries" = Table.NestedJoin(Source2, {"Area", "Cluster"}, #"Unpivoted Other Columns", {"Area", "Value"}, "New", JoinKind.LeftOuter),
    #"Expanded New" = Table.ExpandTableColumn(#"Merged Queries", "New", {"Attribute", "Value"}, {"Attribute", "Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded New",{"Cluster", "Value"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Attribute", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Count")
in
    #"Pivoted Column"

result

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. 

Thank you!

[Mants]

Thank you Omid, much appreciated this helps!

[Ahmedx]

pls try this

let
 f = (w) =>
    Table.ReplaceValue(
      w, 
      (x) => x, 
      (x) => x, 
      (x, y, z) =>
        try List.Select(List.Select(lst, (p) => p{0} = y[Area]), (y) => y{1} = x){0}{2} otherwise 0, 
      List.Skip(Table.ColumnNames(Types))
    ),

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixKTVRwVNJRMgRiIyA2VorVgQpfmATkmwKxGRCbI8SdgVwLILYEaTNQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Area = _t, #"round 1" = _t, #"round 2" = _t, #"round 3" = _t]),
    Types = Table.TransformColumnTypes(Source,{{"Area", type text}, {"round 1", Int64.Type}, {"round 2", Int64.Type}, {"round 3", Int64.Type}}),
    lst = Table.ToList(#"table 2 codes",(x)=>x),
    to =f(Types)
in
    to

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...