Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hiya,
I have a data set that looks like...
user_id | week_start | week_count |
100 | 11/03/2019 | 5 |
100 | 04/03/2019 | 21 |
100 | 25/02/2019 | 27 |
100 | 18/02/2019 | 38 |
100 | 11/02/2019 | 11 |
100 | 04/02/2019 | 34 |
101 | 11/03/2019 | 25 |
101 | 04/03/2019 | 35 |
101 | 25/02/2019 | 27 |
101 | 18/02/2019 | 10 |
101 | 11/02/2019 | 29 |
101 | 04/02/2019 | 30 |
102 | 11/03/2019 | 18 |
102 | 04/03/2019 | 24 |
102 | 25/02/2019 | 40 |
102 | 18/02/2019 | 26 |
102 | 11/02/2019 | 10 |
102 | 04/02/2019 | 14 |
and I'm trying to get it to look more like...
User_id | 11/03/2019 | 04/03/2019 | 25/02/2019 | 18/02/2019 | 11/02/2019 | 04/02/2019 |
100 | 5 | 21 | 27 | 38 | 11 | 34 |
101 | 25 | 35 | 27 | 10 | 29 | 30 |
102 | 18 | 24 | 40 | 26 | 10 | 14 |
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.
Solved! Go to Solution.
Hi @ChrisE87 ,
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
@ChrisE87 Please try using Matrix visual if you want just to view in Pivotted view.
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.
Proud to be a PBI Community Champion
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |