Forum Discussion

jean004's avatar
jean004
Frequent Visitor
4 years ago
Solved

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 ...
Need Help
  • jean004's avatar
    jean004
    4 years ago

    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's avatar
jean004
Frequent Visitor
4 years ago

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's avatar
jbwtp
Memorable Member
4 years ago

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's avatar
    jean004
    Frequent Visitor
    4 years ago

    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's avatar
    jean004
    Frequent Visitor
    4 years ago

    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's avatar
      jbwtp
      Memorable Member
      4 years ago

      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