Joining Columns into 1

[philip1996]

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

[Michal_cwiok · Accepted 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: