Forum Discussion

Steffen1's avatar
Steffen1
Frequent Visitor
3 years ago
Solved

Transforming Data from row to column

Dear Community,   I am looking for a solution to transform a value from a cell into a column, my source is looking as following:      Now I would like to structure the data in columns only...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Steffen1 I did this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY9NCsJADIWvIl2Xkp+ZzGRZaMGFdaFYhNL7X8MMNtWKiF3kTRJevkmmqTr2bddfsKorgQCKxMFyhAawISC2ghUhpMZaVgxtf7MnWlAk08OSzbXDSjdxIUVZDN/jNcI/fe59IlNWEAkhQtmnG5eVGcoR17K8CkYGgZzK/+PZJORVbA6yiOZ/mcOGmdmZiVz2M08bpoIzJbjsZ943UDM6lNVlP5Q+qLieT+ryTp0f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] = "12345678906644500" or [Column1] = "HEADER1")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Column10", each if [Column1] = "HEADER1" then [Column2] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Column11", each if [Column1] = "HEADER1" then [Column3] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Column12", each if [Column1] = "HEADER1" then [Column4] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Column13", each if [Column1] = "HEADER1" then [Column5] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom3",{"Column10", "Column11", "Column12", "Column13"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Column1] = "12345678906644500"))
in
    #"Filtered Rows1"
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Steffen1 I did this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY9NCsJADIWvIl2Xkp+ZzGRZaMGFdaFYhNL7X8MMNtWKiF3kTRJevkmmqTr2bddfsKorgQCKxMFyhAawISC2ghUhpMZaVgxtf7MnWlAk08OSzbXDSjdxIUVZDN/jNcI/fe59IlNWEAkhQtmnG5eVGcoR17K8CkYGgZzK/+PZJORVbA6yiOZ/mcOGmdmZiVz2M08bpoIzJbjsZ943UDM6lNVlP5Q+qLieT+ryTp0f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] = "12345678906644500" or [Column1] = "HEADER1")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Column10", each if [Column1] = "HEADER1" then [Column2] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Column11", each if [Column1] = "HEADER1" then [Column3] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Column12", each if [Column1] = "HEADER1" then [Column4] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Column13", each if [Column1] = "HEADER1" then [Column5] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom3",{"Column10", "Column11", "Column12", "Column13"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Column1] = "12345678906644500"))
in
    #"Filtered Rows1"
Steffen1's avatar
Steffen1
Frequent Visitor
3 years ago

Greg_Deckler,

 

Oh, **bleep**,  this is a so easy solution, I guess I spend too much time thinking on it. 

 

Thank you very much