Select columns starting with

[Anonymous]

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

[Anonymous]

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

[v-juanli-msft]

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

[mahoneypat · Accepted solution]

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]

Thanks very much Pat, it worked perfectly

[Anonymous]

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]

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