Forum Discussion
PowerBI Desktop Report using Dataverse table - mulitple value choice column
I have a column in my Dataverse table called "Skills". It is a choice column and user can input more than one values in the "Skills" column to enter the skills the user is expert in.
I am trying to generate a simple report in PowerBI Desktop against this table. I tried clicking "Publish All Customizations" on my tables, but still the "Skills" column when imported in PBI Desktop, shows only the numbers(844224, 2422133) of the input values instead of showing actual values (.NET, SQL for example)
Can someone please assist? Thank you!
Hi mg9313 ,
I have created a simple sample, please refer to it to see if it helps you.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQxUorVAdEmYNpIxxjEigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"), #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Column1 - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1 - Copy.1", "Column1 - Copy.2", "Column1 - Copy.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1 - Copy.1", Int64.Type}, {"Column1 - Copy.2", Int64.Type}, {"Column1 - Copy.3", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1"}, "Attribute", "Value"), #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns", {"Value"}, Table, {"CODE"}, "Table", JoinKind.LeftOuter), #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"SUBJECT"}, {"SUBJECT"}), #"Grouped Rows" = Table.Group(#"Expanded Table", {"Column1"}, {{"Count", each Text.Combine([SUBJECT],","), type nullable text}}) in #"Grouped Rows"If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi mg9313 ,
I have created a simple sample, please refer to it to see if it helps you.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQxUorVAdEmYNpIxxjEigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"), #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Column1 - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1 - Copy.1", "Column1 - Copy.2", "Column1 - Copy.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1 - Copy.1", Int64.Type}, {"Column1 - Copy.2", Int64.Type}, {"Column1 - Copy.3", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1"}, "Attribute", "Value"), #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns", {"Value"}, Table, {"CODE"}, "Table", JoinKind.LeftOuter), #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"SUBJECT"}, {"SUBJECT"}), #"Grouped Rows" = Table.Group(#"Expanded Table", {"Column1"}, {{"Count", each Text.Combine([SUBJECT],","), type nullable text}}) in #"Grouped Rows"If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.