data transformation

[ashikts]

hi sir,

yesterday you have replied to one of my query yesterday . But that was not enough for me .

Please help to solve it .

I will explain again.

employee-project	hours casted
ashik	48
pbi	40
java	8
rahul	48
java	32
php	32
personal leave	8
holiday	8
amit	40
sql	32
holiday	8

our one table ;ike this with diffrent employee name and their project wise time allocation and holiday or leave allocation.

we have another table 

employee	total casted hours	empid
ashik	48	ina101
rahul	48	ina504
amit	40

result i want is . Please help me sir.im new to this department 

employee	holiday hours	personal leave hours
ashik	8	0
rahul	8	8
amit	8	0

[ImkeF · Accepted solution]

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.

[amitchandak]

@ashikts , that is the issue, there is no way to differentiate between employee name and other names.

@ImkeF , Any solution to this problem. Mixed data employee and metrics are in the same column.

[ashikts]

@ImkeF @amitchandak  thanks sir. It worked .it was very helpful