cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rsanjuan
Helper IV
Helper IV

Dynamic Columns

Not sure if this is possible in PowerBI, but thought I would reach out.  Currently, I have two tables:

 

Capture 1.JPG

 

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

 

Capture 2.JPG

 

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft
Microsoft

@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"


1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Microsoft
Microsoft

@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"


1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@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.

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!