cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Combining data & Pivoting dates

Hiya,

 

I have a data set that looks like...

 

user_idweek_startweek_count
10011/03/20195
10004/03/201921
10025/02/201927
10018/02/201938
10011/02/201911
10004/02/201934
10111/03/201925
10104/03/201935
10125/02/201927
10118/02/201910
10111/02/201929
10104/02/201930
10211/03/201918
10204/03/201924
10225/02/201940
10218/02/201926
10211/02/201910
10204/02/201914

 

and I'm trying to get it to look more like...

 

User_id11/03/201904/03/201925/02/201918/02/201911/02/201904/02/2019
10052127381134
101253527102930
102182440261014

 

I've pivoted based on the week_start column, which has almost got me there but it's showing each user_id on multiple rows, with a value per row per week start - So I could do with combining them into a single row based on the user_id but can't figure that out!

 

Any help would be appreciated!

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Helper IV
Helper IV

Hi @ChrisE87 ,

 

Screenshot (22).png

 

 

Do pivotcolumn based on the week_count column as values in edit queries, you will get the expected output. Let me know whether you the output or not.

 

 

Regards,

-Arul

View solution in original post

3 REPLIES 3
Helper IV
Helper IV

Hi @ChrisE87 ,

 

Screenshot (22).png

 

 

Do pivotcolumn based on the week_count column as values in edit queries, you will get the expected output. Let me know whether you the output or not.

 

 

Regards,

-Arul

View solution in original post

Super User I
Super User I

let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Combining-data-amp-Pivoting-dates/m-p/655202#M314377")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "week_start", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"week_start.1", "week_start.2", "week_start.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"user_id", Int64.Type}, {"week_start.1", Int64.Type}, {"week_start.2", Int64.Type}, {"week_start.3", Int64.Type}, {"week_count", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #date([week_start.3],[week_start.2],[week_start.1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"week_start.1", "week_start.2", "week_start.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", type date}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type1", {{"Custom", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type1", {{"Custom", type text}}, "en-US")[Custom]), "Custom", "week_count")
in
    #"Pivoted Column"

Capture.JPG


New Animated Dashboard: Sales Calendar


Super User I
Super User I

@ChrisE87  Please try using Matrix visual if you want just to view in Pivotted view.

 

image.png

If you want the change the table structure itself then do "Pivot Column" on WeekStart field and value field as WeekCount, in Power Query Editor.

 

image.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors