Help required with PQ

[Alexis36]

Hi All,

I'm having trouble formating the data set attached into columns. I basically need one column for the week Number/Yearand the Quantity but I'm struggling to achieve that with Power Query.

Could someone guide me on how to do it please ?

 

[ExcelToDAX]

can you share a screenshot of how you want the final result to look? Just manually create it

 

[Alexis36]

can you share a screenshot of how you want the final result to look? Just manually create it

it should look like this with Date / Quantity into one column.

 

[GraH]

@Alexis36 ,
Please upload a sample of the raw data using the XL2BB add-on.
Data from a picture are not helping.

 

[GraH]

Column11	column12	column13	column14	column15	column16	column17	column18	column19	column20	column21	column22	column23
48/2023	w	02/2024	w	03/2024	w	04/2024	w	05/2024	w	06/2024	w	07/2024
128		432		432		432		432		336		336
(4)		(4)		(4)		(4)		(4)		(4)		(4)
0150
11/2024	w	12/2024	w	13/2024	w	14/2024	w	15/2024	w	16/2024	w	17/2024
432		576		595		595		595		595		624
(4)		(4)		(4)		(4)		(4)		(4)		(4)
22/2024	w	27/2024	w	31/2024	w	35/2024
1534		836		836		432
(4)		(4)		(4)		(4)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Cols_as_txt = Table.TransformColumnTypes(Source,{{"Column11", type any}, {"column12", type text}, {"column13", type any}, {"column14", type text}, {"column15", type any}, {"column16", type text}, {"column17", type any}, {"column18", type text}, {"column19", type any}, {"column20", type text}, {"column21", type any}, {"column22", type text}, {"column23", type any}}),
    Filter_col13_no_nulls_no4 = Table.SelectRows(Cols_as_txt, each ([column13] <> null and [column13] <> "(4)")),
    Select_alternate_cols = List.Alternate(Table.ToColumns(Filter_col13_no_nulls_no4),1,1,1),
    Transform_to_table = Table.FromRows(List.Split(List.Combine(Select_alternate_cols),2)),
    Rename = Table.RenameColumns(Transform_to_table,{{"Column1", "Week"}, {"Column2", "Value"}}),
    Filter_no_null = Table.SelectRows(Rename, each ([Week] <> null))
in
    Filter_no_null

Week	Value
48/2023	128
11/2024	432
22/2024	1534
02/2024	432
12/2024	576
27/2024	836
03/2024	432
13/2024	595
31/2024	836
04/2024	432
14/2024	595
35/2024	432
05/2024	432
15/2024	595
06/2024	336
16/2024	595
07/2024	336
17/2024	624