Dynamic Columns

[Rsanjuan]

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)?

[v-yuezhe-msft · Accepted solution]

@Rsanjuan,

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

[Rsanjuan]

@v-yuezhe-msft

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.