concatenate Query results row values to create columns

[jean004]

Hello:

I have a scenario where I am trying to create a dynamic way to update the number of columns in particular report.

I have a query that I bring down to result in the number of occurence of a category of purchases.

In my other query, I am trying to pivot the table; however I try and combine columns prior to doing so.  Essentially I pivot on the number of occurances of a category, combine the results of values into one column.   Sometimes it can be 1 or 2 columns, at other times the query could be bringing in up to 10 or more categories.  I need the resulting tables to grow or reduce in size dynamically.

Query Admlistcol results in 1 and 2 values in row 1 and row 2.

1

2

however this table could grow in size (rows - 1,2,3,4,5, etc) or reduce to 1.

I'm trying to use the Table.CombineColumns to essentially add my columns as "1","2"

= Table.CombineColumns(#"Colonne dynamique1",Table.ToRows(Admlistcol,","),Combiner.CombineTextByDelimiter("

", QuoteStyle.None),"Plan")

Thank you!

I keep running into errors.   

[jbwtp]

Hi @jean004,

Would you be able to provide a sample input data and a desired output table, so I could understand better what are you trying to accomplish?

Thnanks,

John 

[jbwtp]

Hi @jean004,

Are you trying to achive something like this? 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYlOlWJ1oJSMgywmIzcA8YyDLGYjNwTwTIMsFiC3APFMgyxWILZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Count = _t, Catogory = _t, Value = _t]),
    #"Merged Columns" = Table.CombineColumns(Source,{"Catogory", "Count"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Count)
in
    #"Pivoted Column"

This is not quite clear form your post. If you have Category and want to calculate they occurance this way, just add another column and then still do the same: pivot using LIst.Count.

Kind regards,

John

[jean004]

Hi John:

Thank you for your reply. 

I will admit that I am a bit outside of my expertise to properly understand the code you have provided, however I'm not sure it is accomplishing what I need.

I am essentially using a pivot table to create a summary by department and service category of various data elements (exp. dates, sourcing, other requirements, purchase term).    The problem I have is that in some cases for the same department, I can have multiple of these summary elements under the same category, and thus I get into list errors.

By running of count of the frequency, I can establish how many of these elements, pivot them them to their own columns, merge and then get a distinct value field by category and department of varying size.  However as items are added or removed, the list can grow or shrink.   

Thus I created a separate query (its evolved since my post) that now establishes a final list product that basically gives me "1","2" (can grow higher if needed).

I now need to call on that list in the second argument of the Table.CombineColumns Fx.

I however get the error Unable to convert ""1","2"" to Type list.

Value = "1","2"

Type = [Type] 

[jbwtp]

Hi @jean004,

I thinke to bea list it should look like" {"1", "2", "3"} rather than just "1", "2", "3'.

Do you mind sharing the piece of code (few lines before [and including] you pass it to Table.CombineColumns) that generates this error?

Thanks,

John

[jean004 · Accepted solution]

I ended up solving my problem.  In the end, I got rid of the additional query Admlistcol.

I modified my code as follows to accomplish what I wanted:

let
Source = #"Contracts Terms",
#"Lignes filtrées" = Table.SelectRows(Source, each ([Catégorie] = "Administration")),
#"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Catégorie"}),
#"Type modifié" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"Plan", type text}}),
#"Lignes groupées" = Table.Group(#"Type modifié", {"Department", "Sous-catégorie"}, {{"Rows", each _, type table [Department=nullable text, #"Sous-catégorie"=nullable text, Plan=nullable text]}}),
Indexed = Table.TransformColumns(#"Lignes groupées", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
#"Rows développé" = Table.ExpandTableColumn(Indexed, "Rows", {"Plan", "GroupIndex"}, {"Plan", "GroupIndex"}),
#"Lignes triées" = Table.Sort(#"Rows développé",{{"Sous-catégorie", Order.Ascending}}),
#"Colonne dynamique1" = Table.Pivot(Table.TransformColumnTypes(#"Lignes triées", {{"GroupIndex", type text}}, "fr-CA"), List.Distinct(Table.TransformColumnTypes(#"Lignes triées", {{"GroupIndex", type text}}, "fr-CA")[GroupIndex]), "GroupIndex", "Plan"),
#"Colonnes fusionnées" = Table.CombineColumns(#"Colonne dynamique1",List.RemoveFirstN(Table.ColumnNames(#"Colonne dynamique1"),2),Combiner.CombineTextByDelimiter("

", QuoteStyle.None),"Plan"),
#"Colonne dynamique" = Table.Pivot(#"Colonnes fusionnées", List.Distinct(#"Colonnes fusionnées"[Department]), "Department", "Plan")
in
#"Colonne dynamique"

[jean004]

Yes, that is my problem right now.

I have this one query to get to my list that I want to bring into the step.

Admlistcol

let
Source = #"Contracts Terms",
#"Lignes filtrées" = Table.SelectRows(Source, each ([Catégorie] = "Administration")),
#"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Catégorie", "Plan"}),
#"Lignes groupées" = Table.Group(#"Colonnes supprimées", {"Department", "Sous-catégorie"}, {{"Rows", each _, type table [Department=nullable text, #"Sous-catégorie"=nullable text]}}),
Indexed = Table.TransformColumns(#"Lignes groupées", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
#"Rows développé" = Table.ExpandTableColumn(Indexed, "Rows", {"Plan", "GroupIndex"}, {"Plan", "GroupIndex"}),
#"Colonnes supprimées1" = Table.RemoveColumns(#"Rows développé",{"Department", "Sous-catégorie", "Plan"}),
#"Doublons supprimés" = Table.Distinct(#"Colonnes supprimées1"),
#"Préfixe ajouté" = Table.TransformColumns(#"Doublons supprimés", {{"GroupIndex", each """" & Text.From(_, "fr-CA"), type text}}),
#"Suffixe ajouté" = Table.TransformColumns(#"Préfixe ajouté", {{"GroupIndex", each _ & """", type text}}),
#"Personnalisée ajoutée" = Table.AddColumn(#"Suffixe ajouté", "lst", each 1),
#"Colonnes renommées" = Table.RenameColumns(#"Personnalisée ajoutée",{{"GroupIndex", "col"}}),
#"Lignes groupées1" = Table.Group(#"Colonnes renommées", {"lst"}, {{"lst.1", each _, type table [col=text, lst=number]}}),
#"Personnalisée ajoutée1" = Table.AddColumn(#"Lignes groupées1", "col", each [lst.1][col]),
#"Colonnes supprimées2" = Table.RemoveColumns(#"Personnalisée ajoutée1",{"lst", "lst.1"}),
#"Valeurs extraites" = Table.TransformColumns(#"Colonnes supprimées2", {"col", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
col = #"Valeurs extraites"{0}[col]
in
col

The results: "1","2","3","4","5","6","7"

I'm trying to reference Admlistcol in my other query get that list to generate.  

let
Source = #"Contracts Terms",
#"Lignes filtrées" = Table.SelectRows(Source, each ([Catégorie] = "Administration")),
#"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Catégorie"}),
#"Type modifié" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"Plan", type text}}),
#"Lignes groupées" = Table.Group(#"Type modifié", {"Department", "Sous-catégorie"}, {{"Rows", each _, type table [Department=nullable text, #"Sous-catégorie"=nullable text, Plan=nullable text]}}),
Indexed = Table.TransformColumns(#"Lignes groupées", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
#"Rows développé" = Table.ExpandTableColumn(Indexed, "Rows", {"Plan", "GroupIndex"}, {"Plan", "GroupIndex"}),
#"Lignes triées" = Table.Sort(#"Rows développé",{{"Sous-catégorie", Order.Ascending}}),
#"Colonne dynamique1" = Table.Pivot(Table.TransformColumnTypes(#"Lignes triées", {{"GroupIndex", type text}}, "fr-CA"), List.Distinct(Table.TransformColumnTypes(#"Lignes triées", {{"GroupIndex", type text}}, "fr-CA")[GroupIndex]), "GroupIndex", "Plan"),
#"Colonnes fusionnées" = Table.CombineColumns(#"Colonne dynamique1",Admlistcol,Combiner.CombineTextByDelimiter("

", QuoteStyle.None),"Plan"),
#"Colonne dynamique" = Table.Pivot(#"Colonnes fusionnées", List.Distinct(#"Colonnes fusionnées"[Department]), "Department", "Plan")
in
#"Colonne dynamique"

[jbwtp]

Hi @jean004,

Good to hear that you were able to find a way to resolve the problem.

I think it could also be resolved by applying Text.Split to a referece to Admlistcol in #"Colonnes fusionnées" step to make it a list:

#"Colonnes fusionnées" = Table.CombineColumns(#"Colonne dynamique1",Text.Split(Admlistcol, ","),Combiner.CombineTextByDelimiter("

", QuoteStyle.None),"Plan"),

Kind regards,

John