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,

 

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"