Power BI - How to combine all the values in a column from another query (table) in Power Query

Question

I'm trying to write a power query to where I can join all the values of a column from another query (table) into a single line.

Example:

Table from Query 1

Outcome: I want it to return a text like:

[2019-09],[2019-10],[2019-11],[2019-12],[2020-01],[2020-02]

I'm trying to put this in my other query, within a JSON code, where the text combine function is (Value).

          {""DataModelName"":""[AllStreams].[Month Year]"",
            ""Caption"":"""&Date.ToText( DateTime.Date( Date.AddDays(DateTimeZone.UtcNow(),0) ) , "yyyy-MM")&""",
            ""Value"":""[" 
                & Text.Combine(Table.SelectColumns(FilterList_PV)[Date]), "" ) & "]"",
            ""Operand"":0,
            ""UnionGroup"":""""}

Let me know if this is possible! Thanks!

horseyride horseyride · Accepted Answer · 2020-03-31T13:15:39

you can do this with one big step, but shown here in smaller pieces

Assuming column in question is named Date

Add a column we can use to group on, all with same value

Add column that converts each date into text surrounded by [] showing year and month

Group on the first added column, combining all row values

Remove extra column

#"Added Custom" = Table.AddColumn(#"PriorStep", "Custom", each "[" & Text.From(Date.Year([Date])) & "-" & Text.PadStart(Text.From(Date.Month([Date])),2,"0") &"]"),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 0),
#"Grouped Rows" = Table.Group(#"Added Index", {"Index"}, {{"Count", each Text.Combine(  List.Transform([Custom], Text.From), ","), type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"})

Power BI - How to combine all the values in a column from another query (table) in Power Query

2 Answers