Forum Discussion
SalesForce source transformation on PowerBI
Hello,
I'm looking to create a report on PowerBI, showing an analysis of the activity of our company's sales staff.
I have several tables, including an "Event" table. This table is linked directly to Salesforce and updates automatically according to triggers.
This table contains the following data:
Id, Subject, IsAllDayEvent, ActivityDateTime, ActivityDate, DurationInMinutes, StartDateTime, EndDateTime, EndDate, Description, AccountId, OwnerId, CurrencyIsoCode, EventSubtype, Contact__c, DB_Activity_Type__c, Event_Status__c, Event_Type_Status__c, Event_Type__c, Out_of_Territory_Time__c, Product_1__c, Product_2__c, Product_3__c, Product_4__c, Event_Id_18__c, Name, StartDateTimeZone, EndDateTimeZone
This is a database from SalesForce. This table contains all the events entered by the company's sales staff. Sales reps enter different types of events: visits to pharmacies/doctors, holidays, illnesses, conferences, etc.
When sales reps enter an event in Salesforce, they must enter the start date and end date of the event. Some events can take place over several whole days (particularly illnesses, holidays, conferences, etc.).
Here is the problem:
A SalesForce extraction line corresponds to an event. Each line of data is a different event. Even if the event lasts several days, Salesforce will only show one line for the event in question, with start and end dates several days apart. This poses a problem for the calculations we want to do later.
I need events taking place over several days to be duplicated on several lines of data, with one event line per day.
Note: The StartDateRime and EndDateTime columns also take hours and minutes into account. The format is: DD/MM/YYYY HH:MM:SS
Up to now, I've been producing my reports in Excel. I had a macro that allowed me to carry out the steps explained above.
Here's an example of what I'd like to achieve:
This is extracted from Salesforce :
Salesperson 00U1v00000l0pQOEAY enters an illness event taking place over several days. SalesForce extracts one line of data. On this line, the event start date is 01/01/2023 08:00:00 and the event end date is 10/01/2023 17:00:00. So the event take place over 10 full days.
What I need:
I need this event to be duplicated on 10 different lines of data (because 10 whole days). Each line must have different activity dates. For example, the first data line will have a start date of 01/01/2023 08:00:00 and an end date of 01/01/2023 17:00:00. The second line should have a start date of 02/01/2023 08:00:00 and an end date of 02/01/2023 17:00:00. And so on until the actual end date of the event, i.e. 10/01/2023, for a total of 10 lines of data.
This should only be done for events taking place over several days. Some events, such as visits, take place on the same day, and usually last only an hour. Events like this one, which take place on the same day, should not be duplicated.
I've been looking for a solution to this problem for a long time now, but there's no way of solving it, especially as I'm not an expert on this software. So I'm open to any solutions you may suggest.
Thank you for your help.
Load your data in a table called Events. Then, in the Power Query Editor, right-click on the Events query in the sidebar and choose Duplicate.
You need to rename this duplicated query to something like ExpandedEvents.Add a new column that calculates the number of days between the StartDateTime and EndDateTime for each event. Use the Duration.Days or similar function.
New Column = Duration.Days([EndDateTime] - [StartDateTime]) + 1Keep only the rows where the calculated duration is greater than 1 day.
Then, for each row in this filtered table, you'll need to create as many duplicates as there are days in the Duration column.
This can be achieved using the List.Numbers function, which creates a list of numbers you can expand into rows:List.Numbers(0, [Duration])
Click the 'expand' button in the column header to turn these lists into individual rows.
You need to update the StartDateTime and EndDateTime for these new rows. You can do this by adding a new custom column:New StartDateTime = Date.AddDays([StartDateTime], [customColumnName])New EndDateTime = Date.AddDays([StartDateTime], [customColumnName]) + Time.From("09:00:00")
(You can adjust the time to 17:00:00 or whatever suits your business logic)Now, you'll have an ExpandedEvents table with rows that are broken down by individual days. Append this table to your original Events table. Go to the original Events query and use the Append Queries feature to append the ExpandedEvents.
After appending, you may want to filter or clean up the columns, like removing the Duration column or the temporary columns you've created for transformations.
1 Reply
- AmiraBedh
Super User
Load your data in a table called Events. Then, in the Power Query Editor, right-click on the Events query in the sidebar and choose Duplicate.
You need to rename this duplicated query to something like ExpandedEvents.Add a new column that calculates the number of days between the StartDateTime and EndDateTime for each event. Use the Duration.Days or similar function.
New Column = Duration.Days([EndDateTime] - [StartDateTime]) + 1Keep only the rows where the calculated duration is greater than 1 day.
Then, for each row in this filtered table, you'll need to create as many duplicates as there are days in the Duration column.
This can be achieved using the List.Numbers function, which creates a list of numbers you can expand into rows:List.Numbers(0, [Duration])
Click the 'expand' button in the column header to turn these lists into individual rows.
You need to update the StartDateTime and EndDateTime for these new rows. You can do this by adding a new custom column:New StartDateTime = Date.AddDays([StartDateTime], [customColumnName])New EndDateTime = Date.AddDays([StartDateTime], [customColumnName]) + Time.From("09:00:00")
(You can adjust the time to 17:00:00 or whatever suits your business logic)Now, you'll have an ExpandedEvents table with rows that are broken down by individual days. Append this table to your original Events table. Go to the original Events query and use the Append Queries feature to append the ExpandedEvents.
After appending, you may want to filter or clean up the columns, like removing the Duration column or the temporary columns you've created for transformations.