Forum Discussion
concatenate Query results row values to create columns
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.
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"
7 Replies
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
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]
