Forum Discussion
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...
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"
Dear AlB , Greg_Deckler,
thank you very much for your very fast replies, please find below the requested:
here the input:
| HEADER1 | 604091234 | 10.01.2023 | 391047.0.0 | MAEU | 5 | 252 | 252 | |
| HEADER2 | 73123456 | |||||||
| HEADER3 | ||||||||
| 12345678906644500 | DV1234 | 301 | S | 196153060876 | VN | 48 | 48 | 4500086698 |
| 12345678906644500 | DV1234 | 301 | M | 196153060883 | VN | 72 | 72 | 4500086698 |
| 12345678906644500 | DV1234 | 301 | L | 196153060890 | VN | 64 | 64 | 4500086698 |
| 12345678906644500 | DV1234 | 301 | XL | 196153060906 | VN | 39 | 39 | 4500086698 |
| 12345678906644500 | DV1234 | 301 | 2XL | 196153060913 | VN | 29 | 29 | 4500086698 |
here the desired outcome:
| 12345678906644500 | DV9742 | 301 | S | 196153060876 | VN | 48 | 48 | 4500086698 | 604091234 | 10.01.2023 | 391047.0.0 | MAEU |
| 12345678906644500 | DV9742 | 301 | M | 196153060883 | VN | 72 | 72 | 4500086698 | 604091234 | 11.01.2023 | 391047.0.1 | MAEU |
| 12345678906644500 | DV9742 | 301 | L | 196153060890 | VN | 64 | 64 | 4500086698 | 604091234 | 12.01.2023 | 391047.0.2 | MAEU |
| 12345678906644500 | DV9742 | 301 | XL | 196153060906 | VN | 39 | 39 | 4500086698 | 604091234 | 13.01.2023 | 391047.0.3 | MAEU |
| 12345678906644500 | DV9742 | 301 | 2XL | 196153060913 | VN | 29 | 29 | 4500086698 | 604091234 | 14.01.2023 | 391047.0.4 | MAEU |
Best Regards
Steffen
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"Oh, **bleep**, this is a so easy solution, I guess I spend too much time thinking on it.
Thank you very much