Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I'm trying to do the following in power bi, but cross join is not working for me, any help would be appriciated
Before
After
Solved! Go to Solution.
I would tackle to problem by splitting the table into two and appending the query.
Paste this code into Advanced Editor in your PowerBI to see the solution.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLTMzDUMzIwNFPSUQoKd3EGUpFKsTpAGSM9AyOYjA9Exg8sY2CqZ2CCJgPRAxQGSmKTAdmDRSYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Severity = _t, isReportable = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Severity", type text}, {"isReportable", type text}}), First_part = Table.RemoveColumns(#"Changed Type",{"isReportable"}), Second_part = Table.RemoveColumns(#"Changed Type",{"Severity"}), Second_part_rename = Table.RenameColumns(Second_part,{{"isReportable", "Severity"}}), #"Appended Query" = Table.Combine({Second_part_rename , First_part}), #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Date", Order.Ascending}}), #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","Y","Reportable",Replacer.ReplaceText,{"Severity"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","N","Not Reportable",Replacer.ReplaceText,{"Severity"}) in #"Replaced Value1"
My final result:
I would tackle to problem by splitting the table into two and appending the query.
Paste this code into Advanced Editor in your PowerBI to see the solution.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLTMzDUMzIwNFPSUQoKd3EGUpFKsTpAGSM9AyOYjA9Exg8sY2CqZ2CCJgPRAxQGSmKTAdmDRSYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Severity = _t, isReportable = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Severity", type text}, {"isReportable", type text}}), First_part = Table.RemoveColumns(#"Changed Type",{"isReportable"}), Second_part = Table.RemoveColumns(#"Changed Type",{"Severity"}), Second_part_rename = Table.RenameColumns(Second_part,{{"isReportable", "Severity"}}), #"Appended Query" = Table.Combine({Second_part_rename , First_part}), #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Date", Order.Ascending}}), #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","Y","Reportable",Replacer.ReplaceText,{"Severity"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","N","Not Reportable",Replacer.ReplaceText,{"Severity"}) in #"Replaced Value1"
My final result:
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |