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 columns gros overtime and the powerbi query needs to be continually updated to include them. Below is an extract of the current data transformation:

 

 

Issues_table = Source{[Name="Issues",Signature="table"]}[Data],
    #"Removed Other Columns1" = Table.SelectColumns(Issues_table,{"[CHART] Time in Status", "Assumptions"}),
    #"Merged Columns - Assumptions" = Table.CombineColumns(#"Removed Other Columns1",{"Assumptions", "Assumptions (1)", "Assumptions (1) (2)", "Assumptions (1) (2) (3)"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Assumptions.1"),
    #"Merged Columns - Business Confirmed by Finance" = Table.CombineColumns(#"Merged Columns - Assumptions",{"Benefits Confirmed by Finance", "Benefits Confirmed by Finance (1)", "Benefits Confirmed by Finance (1) (2)", "Benefits Confirmed by Finance (1) (2) (3)"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Benefits Confirmed by Finance.1"),

 

 

I am wondering whether there is a piece code we can introduce for each steph that would effectively say "all columns starting with "Assumptions" will combine, same for the next step, all columns starting with "Benefits Confirmed by Finance".

 

Do you have any thoughts?

 

Thanks,

OF

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

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    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 Pat,

       

      Thanks for responding. I see what the intent of this is, can you just clarify for me where I use this? Do I add as a step before every merger or do I include it in the merge action when selecting the columns?

       

      Thanks,

      OF

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        Put that code to replace part of the M code of the #merged columns step. Replace the curly bracket list of assumptions column names. The new code will dynamically generate same. 

        Regards

        Pat

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks very much Pat, it worked perfectly

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    If this last reply helps, then please consider Accept it as the solution to help the other members find it more quickly.

    If not, please let me know.

    I'm not clear about your detailed scenario.

    Could you show a simple example like:

     

    Best Regards

    Maggie

  • 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