Copying info from one table to another

Hi, BobPanna ;

You could merge it in power query.

1.Merge it.

2.expand year and employer total.

3.add new column

4.pivot column.

 

The final show:

 

M language.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTIyMLQAU0ZGQMovtVzBvyC1KLEkMz8PyHfOzyvJzCtNLSaSjY5idaKVTM3MITYYWkIsMgRZpO9IinU5+cWpKdjMjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Index 2" = _t, #"lnicia lYe<" = _t, #"Last Year" = _t, #"2018 Status" = _t, #"2019 Status" = _t, #"2020 Status" = _t, #"2021Status" = _t, #"2022 Status" = _t, #"2018 Employers Total" = _t, #"2019 Employers Total" = _t, #"(blank)" = _t, #"2020 Employers Total" = _t, #"2021Employers Total" = _t, #"2022 Employers Total" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index 2", Int64.Type}, {"lnicia lYe<", Int64.Type}, {"Last Year", Int64.Type}, {"2018 Status", type text}, {"2019 Status", type text}, {"2020 Status", type text}, {"2021Status", type text}, {"2022 Status", type text}, {"2018 Employers Total", type text}, {"2019 Employers Total", type text}, {"(blank)", type text}, {"2020 Employers Total", type text}, {"2021Employers Total", type text}, {"2022 Employers Total", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"lnicia lYe<", "lnicia Year"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"2018 Employers Total", "2019 Employers Total", "(blank)", "2020 Employers Total", "2021Employers Total", "2022 Employers Total"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Index 2"}, Table, {"Index2"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Employers Total", "Year"}, {"Table.Employers Total", "Table.Year"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table", "Custom", each Number.ToText( [Table.Year])&" Employers Total"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Table.Year"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Custom]), "Custom", "Table.Employers Total", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"2018 Employers Total", "2019 Employers Total", "2020 Employers Total", "2021 Employers Total", "2022 Employers Total"})
in
    #"Replaced Value"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.