data transformation

Hi ashikts ,

some fill-down and pivot-magic should do the trick:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSizOyMxW0lEysVCK1YlWKkjKBHEMwJysxLJEIA8iU5SYUZqDUAiVMzaCaMsoQOKkFhXn5yXmKOSkJpalwg3IyM/JTEmshPMTczNLEHYVF+YgTEBRGgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"employee-project" = _t, #"hours casted" = _t]),
    EmployessTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSizOyMxW0lEysQASmXmJhgaGSrE60UpFiRmlOUjipgYmYPHE3MwSkLABkFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"employee " = _t, #"total casted hours" = _t, empid = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"employee-project"}, EmployessTable, {"employee "}, "EmployessTable", JoinKind.LeftOuter),
    #"Expanded EmployessTable" = Table.ExpandTableColumn(#"Merged Queries", "EmployessTable", {"employee "}, {"employee "}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded EmployessTable",{{"hours casted", type number}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "employee ", "employee  - Copy"),
    #"Filled Down" = Table.FillDown(#"Duplicated Column",{"employee "}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([#"employee  - Copy"] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"employee  - Copy"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"employee-project"]), "employee-project", "hours casted", List.Sum)
in
    #"Pivoted Column"

Please also check the attached file.