Forum Discussion
Solved
data transformation
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 |
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.
3 Replies
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.
ImkeF amitchandak thanks sir. It worked .it was very helpful
