Stacked Records as a table

Hi Magrfa  lets solve it by using power query (Create blank query)

Create Dataset1: 

let
    Source = Table.FromRows({
        {"A", "Name 1"},
        {"B", "Name 2"},
        {"C", "Name 3"}
    }, {"Label", "Class"}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Label", type text}, 
        {"Class", type text}
    })
in
    #"Changed Type"

Dataset2: 

let
    Source = Table.FromRows({
        {1, "A"},
        {2, "C"},
        {3, "B"},
        {4, "B"},
        {5, "B"},
        {6, "B"},
        {7, "A"},
        {8, "C"},
        {9, "C"},
        {0, "C"}
    }, {"Value", "Label"}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Value", Int64.Type}, 
        {"Label", type text}
    })
in
    #"Changed Type"

Data: 

let
    Dataset1 = #"Dataset1",
    Dataset2 = #"Dataset2",
    
    // Merge to get class names
    #"Merged Queries" = Table.NestedJoin(Dataset2, {"Label"}, Dataset1, {"Label"}, "Lookup", JoinKind.LeftOuter),
    #"Expanded Lookup" = Table.ExpandTableColumn(#"Merged Queries", "Lookup", {"Class"}),
    
    // Group by class and add sequential numbering within each group
    #"Grouped by Class" = Table.Group(#"Expanded Lookup", {"Class"}, {
        {"Data", each Table.AddIndexColumn(_, "GroupIndex", 1, 1, Int64.Type), type table}
    }),
    
    // Expand the grouped data
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped by Class", "Data", 
        {"Value", "GroupIndex"}),
    
    // Pivot by Class using GroupIndex as the row identifier
    #"Pivoted Column" = Table.Pivot(#"Expanded Data", 
        List.Distinct(#"Expanded Data"[Class]), 
        "Class", "Value"),
    
    // Remove the GroupIndex column
    #"Final Result" = Table.RemoveColumns(#"Pivoted Column", {"GroupIndex"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Final Result",{{"Name 1", Int64.Type}, {"Name 3", Int64.Type}, {"Name 2", Int64.Type}})
in
    #"Changed Type"

Output: 

https://drive.google.com/file/d/1pE9ivMLpXs-_1bzRcpuEpcFDw5gQdrTE/view?usp=drive_link 

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!