Forum Discussion
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 and get rid of the 3 header rows.
However I need the data from the HEADER1 row. Therefore I would like to transfer the diffrent values from that row into column´s.
Thank you very much for the support
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"
5 Replies
Steffen1 Can you post that data as text?
Hi Steffen1
Can you share the input and expected output tables? At least the input table in text-tabular format so that its contents can be readily copied
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
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
