cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kattlees
Post Patron
Post Patron

Pivot with multiple values

Wondering if this is possible. I have tried pivot but it gives an error when there is the same date/time and customer.

 

I have a table of 

Customer

Date/Time

Task

 

If a customer has two tasks at the same date/time, I would like it moved into one row like

Customer    Date/Time   Task 1      Task 2

 

If they have two tasks at different dates/times, I would keep it like this

Customer    Date/Time   Task 1

Customer    Date/Time   Task 2

 

CustomerDate/TimeTask 
12345610/20/2020 8:00Task 1 
12345610/20/2020 8:00Task 2 
65432110/20/2020 9:45Task 1 
98754110/20/2020 11:30Task 1 
98754110/20/2020 21:00Task 2 
    
    
    
End Result   
CustomerDate/TimeTask 1Task 2
12345610/20/2020 8:00Task 1Task 2
65432110/20/2020 9:45Task 1 
98754110/20/2020 11:30Task 1 
98754110/20/2020 21:00Task 2 
4 REPLIES 4
BA_Pete
Super User
Super User

Hi @kattlees ,

 

Are there only ever a maximum of two tasks? You could produce exactly what you want in Power Query, but it would take some gymnastics.

 

Would the follwing work instead?

BA_Pete_0-1604072821358.png

 

You can acheive this by just selecting the [Task] field in Power Query, then selecting 'Pivot Column', set 'Values column' to [Date/Time], open advanced options and select 'Don't aggregate'.

 

The broader question is: Why do you want to do this? You are actually moving the data structure away from the most efficient and 'normal' way of storing it.

 

Pete

Not sure I explained myself well.

 

We have a customer come in and do Task A, Task B, Task C (can be up to 4 or 5 tasks)

 

We bill that customer for all tasks and have costs associated with it.

 

Trying to get charges and cost for each customer based on Task to get average charge/cost per task.

 

Charges and cost show by day and aren't assigned to each task individually.

 

Problem is if someone comes in and does task a,b and c and all charges are grouped by day, it skews the totals because they have 3 tasks so the charge/cost is more than somoene who comes in and just does task a.

 

I need the outcome to list a columns with all tasks done, all charges and all costs.

mahoneypat
Super User
Super User

Here is one way to do this in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U9JRMjTQNwIhIwMFCysDA6BISGJxtoKhUqwOYVVGYFVmpibGRoaoqiytTExRzbK0MDc1QVNlaGhlbEBYmZEhqp2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Date/Time" = _t, Task = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer", "Date/Time"}, {{"TaskList", each Text.Combine([Task],";")}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "TaskList", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"TaskList.1", "TaskList.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"TaskList.1", "Task 1"}, {"TaskList.2", "Task 2"}})
in
    #"Renamed Columns"

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


amitchandak
Super User
Super User

@kattlees , Try pivot in power query

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors