cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
philip1996 Frequent Visitor
Frequent 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

classic.PNG

 

 

After

changed.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Michal_cwiok Regular Visitor
Regular Visitor

Re: Joining Columns into 1

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:

post1.png

1 REPLY 1
Michal_cwiok Regular Visitor
Regular Visitor

Re: Joining Columns into 1

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:

post1.png