Forum Discussion

philip1996's avatar
philip1996
Regular Visitor
8 years ago
Solved

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

  • 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: