Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Select columns starting with

Hi PowerBI community,   I am trying to assist one of my colleagues with a powerbi report pulling data from Jira through their documented API.   For a reason not worth explaining, the list of colu...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Yes.  If you look at the M code, the column names to be combined is a list.  You could try putting this M code in place of the existing list (for example for the Assumptions columns)

     

    List.FindText(Table.ColumnNames(#"Removed Other Columns1"), "Assumptions")

     

    That would make a list of all the column names that contain Assumptions.  As it grows, so will that list.

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

Something like this?

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiUyA2A2JzILZQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Assumptions = _t, #"Assumptions (1)" = _t, #"Assumptions (1) (2)" = _t, #"Assumptions (1) (2) (3)" = _t, #"Benefits Confirmed by Finance" = _t, #"Benefits Confirmed by Finance (1)" = _t, #"Benefits Confirmed by Finance (1) (2)" = _t, #"Benefits Confirmed by Finance (1) (2) (3)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Assumptions", type text}, {"Assumptions (1)", type text}, {"Assumptions (1) (2)", type text}, {"Assumptions (1) (2) (3)", type text}, {"Benefits Confirmed by Finance", type text}, {"Benefits Confirmed by Finance (1)", type text}, {"Benefits Confirmed by Finance (1) (2)", type text}, {"Benefits Confirmed by Finance (1) (2) (3)", type text}}),

// Merge only columns that starts with x
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",List.Select(Table.ColumnNames(#"Changed Type"), (x)=> Text.StartsWith(x, "Assumptions")),Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Assumptions"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",List.Select(Table.ColumnNames(#"Changed Type"), (x)=> Text.StartsWith(x, "Benefits Confirmed by Finance")),Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Benefits Confirmed by Finance")
in
    #"Merged Columns1"

 

This uses List.Select(Table.ColumnNames(#"Changed Type"), (x)=> Text.StartsWith(x, "StartsWith...")) to replace a hardcoded list of columns in the Table.CombineColumns steps.

 

Kind regards,

JB