Creating a list from text data in Power Query

[TWEBB]

I am making a simple project management database in Excel with Power Query to merge tables. A goal is to use a text field Project_Update as a data field and pivot results for ease of use. This website led me to use the following DAX formula for the conversion: =CONCATENATEX(Table1,Table1

,", ")[/B] .  This worked well as a strictly Excel pivot. 
[ATTACH type="full"]8054[/ATTACH]

When this functionality needs to be moved through Power Query, the Measure calculation/conversion is still available but the field cannot be added to the pivot.  In what ways does this need to be handled differently in PQ, assuming that it is still possible?

Thank you
Thomas

 

[TWEBB]

I am making a simple project management database in Excel with Power Query to merge tables. A goal is to use a text field Project_Update as a data field and pivot results for ease of use. This website led me to use the following DAX formula for the conversion: =CONCATENATEX(Table1,Table1,", ")[/B] . This worked well as a strictly Excel pivot.

When this functionality needs to be moved through Power Query, the Measure calculation/conversion is still available but the field cannot be added to the pivot. In what ways does this need to be handled differently in PQ, assuming that it is still possible?

Thank you
Thomas

 

[sandy666]

without DAX but Power Query only
so with you example:

Project_Number	Update_Date	Project_Update		Project_Number	01/01/2020	15/01/2020	01/02/2020	15/02/2020
2020-001	01/01/2020	Project started		2020-001	Project started	Update one
2020-001	15/01/2020	Update one		2020-003			Project started	Update one
2020-003	01/02/2020	Project started
2020-003	15/02/2020	Update one

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Project_Number", type text}, {"Update_Date", type date}, {"Project_Update", type text}}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(Type, {{"Update_Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(Type, {{"Update_Date", type text}}, "en-GB")[Update_Date]), "Update_Date", "Project_Update")
in
    Pivot

 

[TWEBB]

Hi Sandy,
Thank you for your quick reply. Please suffer me again. How do I implement that code?

 

[sandy666]

I am making a simple project management database in Excel with Power Query

how you did your project???

Data - New Query - From Other Sources - Blank Query
then replace code in blank query with code from the post
remember the name of the table of your source must be the same as in the pasted code (here : Table1)

be aware that code works

with your example: