Simple Power Query on 3 Columns

[Hzah]

How can I get from this row data, to the following report using Power Query ?

• Raw Data

Title​	Categories​	Due Date​
Finalizing R&R definition ​	Fundamental ​	Jan-21​
Finalizing the effective BI ​	Fundamental ​	Jan-21​
Reevaluating the Mo framework​	Fundamental ​	Jan-21​
Tools MVP2​	Tools​	Aug-21​
Readout​	Communication​	Jan-21​
Townhall​	Communication​	Jan-21​

• After Power Query

Categories	Jun-21	Aug-21
Fundamental	Finalizing R&R definition Finalizing the effective BID Reevaluating the Mo framework
Tools		Tools MVP2
Communication	Readout Townhall

 

[Alex Blakenburg]

Try this:-

• Convert your original data to a table.
• If you call it "tblToDoList" you can use the Code that follows unchanged, otherwise you will need to change the source
• With your cursor anywhere in the table Data > From Table/Range
• In PQ - Home > Advanced Editor
• Select everything that it there and replace it with the code that appears below.
  (if you are using a different table name you could change it at this point, before hitting ok)
• On the right hand side give the query a meaningful name.
  It will also be the name of the output table when you go back to Excel
• In Excel
  • you will need to select all or at least the Data section, format the cells to Wrap.
    I also prefer vertical as Top but you might like one of the other options.
  • you will need to set your column widths
  • Make that format stick for future refreshes: Table Tools -> Design -> External Table Data -> Properties -> Check: “Preserve column sort/filter/layout”
    (that seems to be text book answer but you might need to look at the preserve cell and adjust column width settings as well)
  • I can't see a way of getting this formatting to apply to new columns (months)

References:-
Oz du Soleil - covers doing the equivalent of a TextJoin in PQ here ( the 3 mins from 1min to 4 mins covers it)
Power Query: Combine rows into a single cell with Text.Combine
You will need to replace his "-" delimiter with "#(lf)" - you will need to include the quotes in both cases.

Notes:
Quite a few of the additional steps relate to making the mmm-yy sort correctly in the heading.

let
    Source = Excel.CurrentWorkbook(){[Name="tblToDoList"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Categories", "Due Date"}, {{"Title", each Text.Combine([Title],"#(lf)"
), type text}}),
    #"Added Custom Column Date" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine({"01-", Text.Start([Due Date], 6)}), type text),
    #"Changed Type Date For Sort" = Table.TransformColumnTypes(#"Added Custom Column Date",{{"Custom", type date}}),
    #"Removed Columns Due Date" = Table.RemoveColumns(#"Changed Type Date For Sort",{"Due Date"}),
    #"Sorted Rows Custom Date" = Table.Sort(#"Removed Columns Due Date",{{"Custom", Order.Ascending}}),
    #"Added Due Date As Text" = Table.AddColumn(#"Sorted Rows Custom Date", "Due Date", each Date.ToText([Custom],"MMM-yy")),
    #"Removed Custom Date" = Table.RemoveColumns(#"Added Due Date As Text",{"Custom"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Custom Date", List.Distinct(#"Removed Custom Date"[#"Due Date"]), "Due Date", "Title")
in
    #"Pivoted Column"

 

[Hzah]

Appreciate your help, however as I followed the exact steps I got the following error:
"
An error occurred in the ‘tblToDoList (2)’ query. Expression.Error: The column 'Title' of the table wasn't found.
Details:
Title"


The "Title" column is there though. Could you share the final excel including the PQ.

 

[Alex Blakenburg]

Here is my workings file >
20210601 PQ TextJoin Merge Rows Sent.xlsx

 

[Hzah]

Here is my workings file >
20210601 PQ TextJoin Merge Rows Sent.xlsx

Thank you so much!! It worked beautifully.

One note here is that the "due date" is taken as a text field in your excel file.
However in most cases the dates are in regular "mm/dd/yyyy" format and we want to pivot based on "mm/yy" (as below picture)
What changes should we make?

 

[Alex Blakenburg]

Assuming you still want the merging of the Title text to be at the month level, see if this works.
(back up your current version first, it has changed the code quite a bit)

let
    Source = Excel.CurrentWorkbook(){[Name="tblToDoList"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Due Date", type date}, {"Categories", type text}}),
    #"Add Cust Col StartOfMth" = Table.AddColumn(#"Changed Type", "Custom", each Date.StartOfMonth([Due Date]), type date),
    #"Removed Columns DueDate" = Table.RemoveColumns(#"Add Cust Col StartOfMth",{"Due Date"}),
    #"Renamed Columns to DueDate" = Table.RenameColumns(#"Removed Columns DueDate",{{"Custom", "Due Date"}}),
    #"Grouped Rows JoinTitle" = Table.Group(#"Renamed Columns to DueDate", {"Categories", "Due Date"}, {{"Title", each Text.Combine([Title],"#(lf)"
), type text}}),
    #"Sorted Rows Date" = Table.Sort(#"Grouped Rows JoinTitle",{{"Due Date", Order.Ascending}}),
    #"Inserted DueMthYear" = Table.AddColumn(#"Sorted Rows Date", "Due MonthYear", each Date.ToText([Due Date],"MMM")&"-"&Date.ToText([Due Date],"yy"),type text),
    #"Pivoted Column byMthYear" = Table.Pivot(#"Inserted DueMthYear", List.Distinct(#"Inserted DueMthYear"[#"Due MonthYear"]), "Due MonthYear", "Title"),
    #"Removed Columns DueDateCust" = Table.RemoveColumns(#"Pivoted Column byMthYear",{"Due Date"})
in
    #"Removed Columns DueDateCust"

 

[Hzah]

unfortunately did not work (changed the due date column from "general" to "date", wine new dates entered "e.g. 06/21/2021") . I am getting weird error. Can it caused different excel version( I am using 2016)? Could you share your file as well

 

[Alex Blakenburg]

Can you provide an XL2BB of your data (subset is fine) and also what your table name is that you are using ?
Can you check that the when you click the "Add Cust Col StartOfMth" step the dates in both columns look ok and none are showing as Error ?

 

[Alex Blakenburg]

Please make sure that your Power Query Regional Settings > query options,
are set to a Locale that is consistent with the date format that you are using.

 

[Alex Blakenburg]

Link to my working file v02 with the date change requested.
20210601 PQ TextJoin Merge Rows Sent v02.xlsx