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 solution for the same.

 

Currently geting data from PLC are show in below image

 

Need to tranform the data as of below image. 

 

 

Data Protection
Need Help
Tips and Tricks

2 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    hi, Sattu_98 

     

     

     

    let
    Source = currently_getting,
    columns = Table.ToColumns(Source),
    split = List.Split(columns, 6),
    combine = List.Transform(List.Zip(split), List.Combine),
    to_tbl = Table.FromColumns(combine, List.FirstN(Table.ColumnNames(Source), 6))
in
    to_tbl

     

     

    or 

     

     

    let
    Source = currently_getting,
    z = 
        Table.FromList(
            List.Combine(
                Table.ToList(Source, (x) => List.Split(x, 6))
            ), 
            (w) => w, 
            List.FirstN(Table.ColumnNames(Source), 6)
        )
in 
    z

     

     

     

  • tackytechtom's avatar
    tackytechtom
    Most 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/