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 all,
I have couple of challenges, I feel that the second one may not be possible in PBI based on my research but any alternatives would be appreciated.
1) Trying to slice the column based data. For this I have created a calculated column which did work but the issue is I have 25 columns that need to be filtered based on the slicer selection and I will end up creating 25 calc columns. Here is my calculation : IF(Table[AUR]="DA",Table[ABC_BOT_DA],BLANK())
2) I am trying to display slicer selection related fields only but not other fields. For example if the user selects "DA" from the AUR slicer only DA related fields should be displayed in the grid visual and MO related fields should be hidden.
Sample Data:
Customer | AUR | ABC BOT DA | ABC H DA | ABC T DA | FB H DA | FB B DA | FB T DA | ABC H MO | ABC B MO | ABC T MO | FB H MO | FB B MAO | FB T MO |
XYZ | DA | 123 | 52 | 69 | |||||||||
ABC | MO | 225 | 2144 | 125 |
Desired output:
Customer | AUR | ABC Bot | ABC Human | ABC Tango | FB Human | FB Bot | FB Tango | |
XYZ | DA | 123 | 52 | 69 | ||||
ABC | MO | 225 | 2144 | 125 |
Thanks in advance
Solved! Go to Solution.
I would solve this in the query editor. Your starting data is in a wide format which makes it far harder to work with.
I have done these transformations in the below M Code.
In my example the ABC humans and ABC bot are the other way around, as i think there is a typo in one of your original tables.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiMUtJRcnEEEoZGxkDS1AhImFkCCQWCOFYnWsnRyRnI9vXHo9DIyBREGpqYgG0xRTUjFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, AUR = _t, #"ABC B DA" = _t, #"ABC H DA" = _t, #"ABC T DA" = _t, #"FB H DA" = _t, #"FB B DA" = _t, #"FB T DA" = _t, #"ABC H MO" = _t, #"ABC B MO" = _t, #"ABC T MO" = _t, #"FB H MO" = _t, #"FB B MAO" = _t, #"FB T MO" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer", "AUR"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute", type text}, {"Attribute.2", type text}, {"Value", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Attribute.2] = [AUR])),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.2"})
in
#"Removed Columns"
In order to run this create a blank query then open it in the advanced editor and paste the code in.
To visualize this in power bi create a matrix visual with customer on the rows, attribute on columns and value on the values
Hopefully this answers your question
I would solve this in the query editor. Your starting data is in a wide format which makes it far harder to work with.
I have done these transformations in the below M Code.
In my example the ABC humans and ABC bot are the other way around, as i think there is a typo in one of your original tables.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiMUtJRcnEEEoZGxkDS1AhImFkCCQWCOFYnWsnRyRnI9vXHo9DIyBREGpqYgG0xRTUjFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, AUR = _t, #"ABC B DA" = _t, #"ABC H DA" = _t, #"ABC T DA" = _t, #"FB H DA" = _t, #"FB B DA" = _t, #"FB T DA" = _t, #"ABC H MO" = _t, #"ABC B MO" = _t, #"ABC T MO" = _t, #"FB H MO" = _t, #"FB B MAO" = _t, #"FB T MO" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer", "AUR"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute", type text}, {"Attribute.2", type text}, {"Value", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Attribute.2] = [AUR])),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.2"})
in
#"Removed Columns"
In order to run this create a blank query then open it in the advanced editor and paste the code in.
To visualize this in power bi create a matrix visual with customer on the rows, attribute on columns and value on the values
Hopefully this answers your question
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.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |