Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I am looking for the output similar to one above using either Power Query or DAX.
Cheers,
Solved! Go to 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"
I have found a solution for the same in DAX and it works well.
Cheers,
Rutika
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"
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.
"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?
Thanks @Anonymous
It is just an example created for understanding but it should have displayed the output as "ABC:|ABC:|ABC
Cheers,
R
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"
Thanks @Anonymous i will try it and see if it works.
@Anonymous The source table is from SQL DB, will the above code work for same?
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
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.