Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
This is what I intend to do:
https://1drv.ms/x/s!AuHocvOERo6IhNl9pWhLKdaytFAvsQ?e=TYjrwv
Perhaps the way I'm going about solving this issue is not the correct one. I'm open to suggestions.
Thank you
Solved! Go to Solution.
Hi @Berean_50
I am bit confused about row number 3 in step 1 of yours. This where you have ID as 100 but name as Mary. But other than below code seems to work. I will wait for your feedback.
Input:
Result: (i could not take screenshot with all columns)
Code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJR8srPyANShkDsWJqSWQKkA1LzUjLz0oEsv3ylWB0sKl1TSpMTSzLzQXzn/NyCnNSS1BQgOzK1GKzBCKzBN7GoEkgZ4TMaQyWQmZ1aAlGDYXQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Name = _t, #"Period Number" = _t, #"Category Name" = _t, #"Category Status" = _t, #"Category Requiment Met" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Name", type text}, {"Period Number", Int64.Type}, {"Category Name", type text}, {"Category Status", type text}, {"Category Requiment Met", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Category name with status", each [Category Name] & " - Status"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Category Name] & " - Requirements met"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Category Name"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Category name with status"]), "Category name with status", "Category Status"),
#"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Custom]), "Custom", "Category Requiment Met")
in
#"Pivoted Column1"
accpet it as solution if it works for you
Thanks
thingsclump
Hi @Berean_50
I am bit confused about row number 3 in step 1 of yours. This where you have ID as 100 but name as Mary. But other than below code seems to work. I will wait for your feedback.
Input:
Result: (i could not take screenshot with all columns)
Code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJR8srPyANShkDsWJqSWQKkA1LzUjLz0oEsv3ylWB0sKl1TSpMTSzLzQXzn/NyCnNSS1BQgOzK1GKzBCKzBN7GoEkgZ4TMaQyWQmZ1aAlGDYXQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Name = _t, #"Period Number" = _t, #"Category Name" = _t, #"Category Status" = _t, #"Category Requiment Met" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Name", type text}, {"Period Number", Int64.Type}, {"Category Name", type text}, {"Category Status", type text}, {"Category Requiment Met", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Category name with status", each [Category Name] & " - Status"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Category Name] & " - Requirements met"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Category Name"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Category name with status"]), "Category name with status", "Category Status"),
#"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Custom]), "Custom", "Category Requiment Met")
in
#"Pivoted Column1"
accpet it as solution if it works for you
Thanks
thingsclump