Forum Discussion

Duffy789's avatar
Duffy789
New Member
3 years ago
Solved

Swapping cells values over in 2 colums

Hi, I have 2 columns and the data value needs to be swapped around, this is due to the source not been ideal, it is not possible to sort the source out.

 

See below what I woudl like is the the date under Tag_Zone_Org to be swapped over with the "1" under Tag_Date_Created_Org

I am pretty new to PowerBI so might need a very easy guide.

 

Thanks 

 

 

 

Need Help
  • danextian's avatar
    danextian
    3 years ago

    Hi Duffy789 ,

    You can use try <expresson> otherwise <expression> to parse the correct value in the needed format. For example

    try Date.From([Date]) otherwise Date.From([Zone])

    This will parse the date from your supposed to be date column otherwise will parse the date from Zone column. HEre's a sample M code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy1zUw1zUyMDJUitWJVjKw1DWwAHGNQHJgISQVOkrGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Zone = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Zone", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date2", each try Date.From([Date]) otherwise Date.From([Zone]), type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each try Number.From([Date]) otherwise Number.From([Zone]), Int64.Type)
in
    #"Added Custom1"

     

     

     

3 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    3 years ago

    Hi Duffy789 ,

    You can use try <expresson> otherwise <expression> to parse the correct value in the needed format. For example

    try Date.From([Date]) otherwise Date.From([Zone])

    This will parse the date from your supposed to be date column otherwise will parse the date from Zone column. HEre's a sample M code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy1zUw1zUyMDJUitWJVjKw1DWwAHGNQHJgISQVOkrGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Zone = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Zone", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date2", each try Date.From([Date]) otherwise Date.From([Zone]), type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each try Number.From([Date]) otherwise Number.From([Zone]), Int64.Type)
in
    #"Added Custom1"

     

     

     

  • Duffy789's avatar
    Duffy789
    New Member
    3 years ago

    Thanks, little bit of fiddling etc but that has done it 

  • Duffy789's avatar
    Duffy789
    New Member
    3 years ago

    danextian 

     

    Sorry I managed to do one but i have several other files to do, when I cut and paste the code to another table (which uses a different source file) it is refering to the data from the orginal table, it is like it is using that as the source, how do I change the source to read from the new table/ the other file?

     

    I think the issue is the table in the query was created manually so that is always goign to return those dates, how do i change the source 

    Thanks