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"

jbwtp's avatar
jbwtp
Memorable Member
4 years ago

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'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"