Combining data & Pivoting dates

[ChrisE87]

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.

[PattemManohar]

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

[smpa01]

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"

[Arul · Accepted 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