Forum Discussion
philip1996
8 years agoRegular Visitor
Joining Columns into 1
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
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:
1 Reply
- Michal_cwiokResolver II
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: