Print the column values with multiple times like concatenate

[Anonymous]

Hi there, 

I am looking for the output similar to one above using either Power Query or DAX.

Cheers,

[Anonymous · Accepted solution]

this code dynamically applies logic to all columns subsequent to the first.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYgOlWJ1oMMsYzoOwdJRMwDwTKM8YzDOFqjRSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [key = _t, ABC = _t, XYZ = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ABC", Int64.Type}, {"XYZ", Int64.Type}}),
    cols=Table.ColumnNames(#"Changed Type"),
    nCols=List.Count(cols),

   #"Added Custom" = Table.AddColumn(#"Changed Type", "out", (r)=>
       Text.AfterDelimiter( Text.Combine(List.Transform({1..nCols-1}, (c)=> Text.Repeat(":|"&cols{c},Record.FieldValues(r){c}) )),":|"))

in
    #"Added Custom"

[Anonymous]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYgOlWJ1oMMsYzoOwdJRMwDwTKM9YKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [key = _t, ABC = _t, XYZ = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ABC", Int64.Type}, {"XYZ", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "out", each Text.AfterDelimiter(Text.Repeat(":|ABC",[ABC]) &Text.Repeat(":|XYZ",[XYZ]),":|"))
in
    #"Added Custom"

if you don't need a more general solution that doesn't rigidly depend on the name of the columns,

an unclear point for me is the fact that only a few lines must have text. but it is not explained which or by what criteria to choose

[Anonymous]

Hi @Anonymous 

the logic is it should be based on the value present in the respective column, if it is 0 then no need to display concatenate value.

[Anonymous]

"the logic is it should be based on the value present in the respective column, if it is 0 then no need to display concatenate value."

why, then, is the second line empty?

[Anonymous]

Thanks @Anonymous 

It is just an example created for understanding but it should have displayed the output as "ABC:|ABC:|ABC

Cheers,

R

[Anonymous · Accepted solution]

this code dynamically applies logic to all columns subsequent to the first.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYgOlWJ1oMMsYzoOwdJRMwDwTKM8YzDOFqjRSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [key = _t, ABC = _t, XYZ = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ABC", Int64.Type}, {"XYZ", Int64.Type}}),
    cols=Table.ColumnNames(#"Changed Type"),
    nCols=List.Count(cols),

   #"Added Custom" = Table.AddColumn(#"Changed Type", "out", (r)=>
       Text.AfterDelimiter( Text.Combine(List.Transform({1..nCols-1}, (c)=> Text.Repeat(":|"&cols{c},Record.FieldValues(r){c}) )),":|"))

in
    #"Added Custom"

[Anonymous]

Thanks @Anonymous i will try it and see if it works.

[Anonymous]

@Anonymous The source table is from SQL DB, will the above code work for same?

[v-juanli-msft]

Hi @Anonymous 

Do you use direct query mode or import mode?

If it is direct query, maybe some functions don't work.

Best Regards

Maggie

[Anonymous · Accepted solution]

Hi @v-juanli-msft 

I have found a solution for the same in DAX and it works well.

Cheers,

Rutika

[Anonymous]

I don't know all the different ways PBI loads data from different prigines. It is only recently that I started using power queries to do some things I previously did in Excel. For now I have only experienced with data loaded from csv or excel files.

That said, I can't imagine why, once loaded into PBI, a table should behave differently depending on its origin.

Therefore I believe the above code works (or doesn't work) the same way for SQL tables or other sources.

 you should just append the following code after your code that loads the SQL data, relabeling last step tableSQL or changing tableSQL to match the label of your last step.

...

    cols=Table.ColumnNames(tableSQL),
    nCols=List.Count(cols),

   #"Added Custom" = Table.AddColumn(#"Changed Type", "out", (r)=>
       Text.AfterDelimiter( Text.Combine(List.Transform({1..nCols-1}, (c)=> Text.Repeat(":|"&cols{c},Record.FieldValues(r){c}) )),":|"))

in
    #"Added Custom"