Forum Discussion

Sattu_98's avatar
Sattu_98
New Member
2 years ago

how to transform the data having different component in rows?

Hi,    I am trying to transform the data in which major compoenent are in row need that comomnet in cloume. I am attaching image for the same. I am recving this data from PLC. Kindly provide the so...
data protection
Need Help
Tips and Tricks
tackytechtom's avatar
tackytechtom
Icon for Most Valuable Professional rankMost Valuable Professional
2 years ago

Hi Sattu_98 ,

 

Here a solution with an own example.

 

Before:

 

After:

 

Here the M Code for that example. Note, you can see the steps I took on the right hand side (green). Also, the trick is to fork out the code by referencing steps that lay higher up the chain (see yellow and orange)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzTUNzIwMlbSUXIEYicgdgZiFyB2BWI3pdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Column1A = _t, Column2A = _t, Column3A = _t, Column1B = _t, Column2B = _t, Column3B = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type date}, {"Column1A", type text}, {"Column2A", type text}, {"Column3A", type text}, {"Column1B", type text}, {"Column2B", type text}, {"Column3B", type text}}),
    #"Removed Columns 1" = Table.RemoveColumns(#"Changed Type",{"Column1B", "Column2B", "Column3B"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns 1",{{"Column1A", "Column1"}, {"Column2A", "Column2"}, {"Column3A", "Column3"}}),
    #"Removed Columns 2" = Table.RemoveColumns(#"Changed Type",{"Column1A", "Column2A", "Column3A"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns 2",{{"Column1B", "Column1"}, {"Column2B", "Column2"}, {"Column3B", "Column3"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Renamed Columns1"})
in
    #"Appended Query"

 

Hope this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/