Transforming Data from row to column

[Steffen1]

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]

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

[Greg_Deckler · Accepted solution]

@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]

@Greg_Deckler,

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

Thank you very much

[AlB]

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.

[Greg_Deckler]

@Steffen1 Can you post that data as text?