Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Not sure if this is possible in PowerBI, but thought I would reach out. Currently, I have two tables:
Some of the job numbers have multiple project managers but most job numbers only have 1. After the total billable hours, there are 4 blank columns that were added for Excel analysis later on. There is only one Project Manager field currently.
Is it possible to layout the data like this where Project Manager 2 and Project Manager 3 field would be filled in with name of Project Manager if there are multiple project managers? If there is only one Project Manager, only Project Manager 1 field would populate and Project Manager 2/Project Manger 3 would be null (blank)?
Solved! Go to Solution.
You can achieve the above requirement by adding the following code in Advanced Editor. Replace the Source part code(second line) with your own table source.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUYoKBhI+AUDCK7UoNbcSyDAx1DNVitVBl/fLTM4GUoZYpPwT80ACEF1JQJYLSCrAAyRfmgwy01jPHCKbDOS4gWQjwbKVeXlAykgPm2RwZm4+SNYMJBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Job Number" = _t, Account = _t, #"End Client" = _t, #"Project Manager" = _t, #"Total Billable Hours" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Number", type text}, {"Account", type text}, {"End Client", type text}, {"Project Manager", type text}, {"Total Billable Hours", type number}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Total Billable Hours", type text}}, "en-US"),{"Project Manager", "Total Billable Hours"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged") , AddRanking = (table, column, newColumn) => Table.AddIndexColumn(Table.Sort(table, {{column, Order.Descending}}), newColumn, 1, 1), #"Grouped Rows" = Table.Group(#"Merged Columns", {"Job Number"}, {{"Data", each _, type table}}), Transformed = Table.TransformColumns(#"Grouped Rows", {{"Data", each AddRanking(_, "Merged", "Rank")}}), #"Expand Data" = Table.ExpandTableColumn(Transformed, "Data", {"Account", "End Client", "Merged", "Rank"}, {"Account", "End Client", "Merged", "Rank"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US")[Rank]), "Rank", "Merged"), #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "3", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"3.1", "3.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"3.1", type text}, {"3.2", type number}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"2.1", "2.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"2.1", type text}, {"2.2", type number}}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"1.1", "1.2"}), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter2",{{"1.1", "Project Manager1"}, {"1.2", "Manager1 Billable Hours"}, {"2.1", "Project Manager2"}, {"3.1", "Project Manager3"}, {"2.2", "Manager2 Billable Hours"}, {"3.2", "Manager3 Billable Hours"}}), #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Manager1 Billable Hours", Currency.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type3", "Total Billable Hours", each [Manager1 Billable Hours]+[Manager2 Billable Hours]+[Manager3 Billable Hours]) in #"Added Custom"
Regards,
Lydia
You can achieve the above requirement by adding the following code in Advanced Editor. Replace the Source part code(second line) with your own table source.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUYoKBhI+AUDCK7UoNbcSyDAx1DNVitVBl/fLTM4GUoZYpPwT80ACEF1JQJYLSCrAAyRfmgwy01jPHCKbDOS4gWQjwbKVeXlAykgPm2RwZm4+SNYMJBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Job Number" = _t, Account = _t, #"End Client" = _t, #"Project Manager" = _t, #"Total Billable Hours" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Number", type text}, {"Account", type text}, {"End Client", type text}, {"Project Manager", type text}, {"Total Billable Hours", type number}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Total Billable Hours", type text}}, "en-US"),{"Project Manager", "Total Billable Hours"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged") , AddRanking = (table, column, newColumn) => Table.AddIndexColumn(Table.Sort(table, {{column, Order.Descending}}), newColumn, 1, 1), #"Grouped Rows" = Table.Group(#"Merged Columns", {"Job Number"}, {{"Data", each _, type table}}), Transformed = Table.TransformColumns(#"Grouped Rows", {{"Data", each AddRanking(_, "Merged", "Rank")}}), #"Expand Data" = Table.ExpandTableColumn(Transformed, "Data", {"Account", "End Client", "Merged", "Rank"}, {"Account", "End Client", "Merged", "Rank"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US")[Rank]), "Rank", "Merged"), #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "3", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"3.1", "3.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"3.1", type text}, {"3.2", type number}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"2.1", "2.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"2.1", type text}, {"2.2", type number}}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"1.1", "1.2"}), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter2",{{"1.1", "Project Manager1"}, {"1.2", "Manager1 Billable Hours"}, {"2.1", "Project Manager2"}, {"3.1", "Project Manager3"}, {"2.2", "Manager2 Billable Hours"}, {"3.2", "Manager3 Billable Hours"}}), #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Manager1 Billable Hours", Currency.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type3", "Total Billable Hours", each [Manager1 Billable Hours]+[Manager2 Billable Hours]+[Manager3 Billable Hours]) in #"Added Custom"
Regards,
Lydia
Hi,
First, thanks for the reply. Trying to figure out where to insert that code into the existing one below I have in advanced editor.
let
Source = Salesforce.Data(),
Timesheets__c = Source{[Name="Timesheets__c"]}[Data],
#"Merged Queries" = Table.NestedJoin(Timesheets__c,{"Employee2__c"},#"Timesheet User",{"Id"},"NewColumn",JoinKind.LeftOuter),
#"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"NewColumn", "TSUser"}}),
#"Expanded TSUser" = Table.ExpandTableColumn(#"Renamed Columns", "TSUser", {"CreatedDate", "CreatedById", "Name__c", "Employee_ID__c", "Employee_Type__c"}, {"TSUser.CreatedDate", "TSUser.CreatedById", "TSUser.Name__c", "TSUser.Employee_ID__c", "TSUser.Employee_Type__c"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded TSUser", each true),
#"Merged Queries1" = Table.NestedJoin(#"Filtered Rows",{"CreatedById"},User,{"Id"},"NewColumn",JoinKind.LeftOuter),
#"Renamed Columns1" = Table.RenameColumns(#"Merged Queries1",{{"NewColumn", "User"}}),
#"Expanded User" = Table.ExpandTableColumn(#"Renamed Columns1", "User", {"Location__c", "Name"}, {"User.Location__c", "User.Name"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded User",{"Related_Job_s__c"},Job,{"Id"},"NewColumn",JoinKind.LeftOuter),
#"Renamed Columns2" = Table.RenameColumns(#"Merged Queries2",{{"NewColumn", "Job"}}),
#"Expanded Job" = Table.ExpandTableColumn(#"Renamed Columns2", "Job", {"Account__c", "Account_Name__c", "Id", "J_T__c", "Job_End_Date__c", "Job_Start_Date__c", "Name", "Opportunity__c"}, {"Job.Account__c", "Job.Account_Name__c", "Job.Id", "Job.J_T__c", "Job.Job_End_Date__c", "Job.Job_Start_Date__c", "Job.Name", "Job.Opportunity__c"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Job",{"IsDeleted", "CreatedDate", "LastModifiedDate", "LastModifiedById", "SystemModstamp", "LastViewedDate", "LastReferencedDate", "Comments__c", "Linked_Account__c"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Week_Of__c", "Account__c", "Master_Project__c", "Related_Job_s__c", "Employee_Name__c", "Account_Manager__c", "COE__c", "Job_Name_Subject__c", "TSUser.Name__c", "TSUser.Employee_ID__c", "TSUser.Employee_Type__c", "User.Location__c", "User.Name", "Job.Account__c", "Job.Account_Name__c", "Job.Id", "Job.J_T__c", "Job.Job_End_Date__c", "Job.Job_Start_Date__c", "Job.Name", "Employee2__c", "Job.Opportunity__c", "Timecode__c"}, "Attribute", "Value"),
#"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Number", each if [Attribute] = "Monday__c" then "1" else if [Attribute] = "Tuesday__c" then "2" else if [Attribute] = "Wednesday__c" then "3" else if [Attribute] = "Thursday__c" then "4" else if [Attribute] = "Friday__c" then "5" else if [Attribute] = "Saturday__c" then "6" else if [Attribute] = "Sunday__c" then "0" else null ),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Number", Int64.Type}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type","5/29/2017","5/28/2017",Replacer.ReplaceText,{"Employee2__c"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "Date", each Date.AddDays([Week_Of__c],+[Number])),
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Week_Of__c", Order.Ascending}}),
#"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([Number] <> null)),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Value", Currency.Type}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type1",{{"Value", "Hours"}, {"User.Name", "Project Manager"}, {"Job.Name", "Job Name"}, {"Job.Job_Start_Date__c", "Job Start Date"}, {"Job.Job_End_Date__c", "Job End Date"}, {"Job.Account_Name__c", "Account"}}),
#"Merged Queries3" = Table.NestedJoin(#"Renamed Columns3",{"Master_Project__c"},#"Master Project",{"Id"},"NewColumn",JoinKind.LeftOuter),
#"Renamed Columns4" = Table.RenameColumns(#"Merged Queries3",{{"NewColumn", "MP"}}),
#"Expanded MP" = Table.ExpandTableColumn(#"Renamed Columns4", "MP", {"Name"}, {"MP.Name"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded MP","Nick long","Nick Long",Replacer.ReplaceText,{"Project Manager"}),
#"Renamed Columns5" = Table.RenameColumns(#"Replaced Value",{{"Master_Project__c", "Master Project ID"}, {"MP.Name", "Master Project Name"}}),
#"Merged Queries4" = Table.NestedJoin(#"Renamed Columns5",{"Job.Opportunity__c"},Opportunity,{"Id"},"NewColumn",JoinKind.LeftOuter),
#"Renamed Columns6" = Table.RenameColumns(#"Merged Queries4",{{"NewColumn", "Opp"}}),
#"Expanded Opp" = Table.ExpandTableColumn(#"Renamed Columns6", "Opp", {"Programming_checkbox__c"}, {"Opp.Programming_checkbox__c"}),
#"Renamed Columns7" = Table.RenameColumns(#"Expanded Opp",{{"Opp.Programming_checkbox__c", "Programming"}}),
#"Filtered Rows2" = Table.SelectRows(#"Renamed Columns7", each [Week_Of__c] > #date(2016, 5, 1)),
#"Renamed Columns8" = Table.RenameColumns(#"Filtered Rows2",{{"Hours", "Hour"}, {"Timecode__c", "Timecode"}}),
#"Added Conditional Column1" = Table.AddColumn(#"Renamed Columns8", "Timecode 2", each if [Timecode] = "Bank Holiday" then "PTO" else if [Timecode] = "PTO" then "PTO" else null ),
#"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column1",{"Employee2__c", "Week_Of__c", "Account__c", "Timecode", "Timecode 2", "Master Project ID", "Related_Job_s__c", "Account_Manager__c", "COE__c", "Employee_Name__c", "Job_Name_Subject__c", "TSUser.Name__c", "TSUser.Employee_ID__c", "TSUser.Employee_Type__c", "User.Location__c", "Project Manager", "Job.Account__c", "Account", "Job.Id", "Job.J_T__c", "Job End Date", "Job Start Date", "Job Name", "Job.Opportunity__c", "Attribute", "Hour", "Number", "Date", "Master Project Name", "Programming"}),
#"Filtered Rows3" = Table.SelectRows(#"Reordered Columns", each [Week_Of__c] > #date(2016, 4, 30)),
#"Merged Queries5" = Table.NestedJoin(#"Filtered Rows3",{"Project Manager"},#"Billable Rate",{"Project Manager"},"Billable Rate",JoinKind.LeftOuter),
#"Expanded Billable Rate" = Table.ExpandTableColumn(#"Merged Queries5", "Billable Rate", {"Billable Rate 1", "Billable Rate 2", "Billable Rate 3", "Total Billable to Job"}, {"Billable Rate.Billable Rate 1", "Billable Rate.Billable Rate 2", "Billable Rate.Billable Rate 3", "Billable Rate.Total Billable to Job"}),
#"Renamed Columns9" = Table.RenameColumns(#"Expanded Billable Rate",{{"Billable Rate.Billable Rate 1", "Billable Rate 1"}, {"Billable Rate.Billable Rate 2", "Billable Rate 2"}, {"Billable Rate.Billable Rate 3", "Billable Rate 3"}, {"Billable Rate.Total Billable to Job", "Total Billable to Job"}})
in
#"Renamed Columns9"
There are two tables that I am using that have a relationship: Timesheet and Job. They have a relationship through the field "Related_Job_s__c" in Timesheet and "Id" in Job.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
121 | |
112 | |
59 | |
58 |