How to move only in row to be a column

[Anonymous]

Hi everyone,

I would really appreciate your help. I have this data that we get as a csv every month and it is formatted weird. I would like to use Power Query to make the "Zeitraum" a new column and the 22022 should be the values below for every row.

The 22022 in this case stands for February 2022. I would also need this field to be transformed to be 01.02.2022 (European Format, first of month so that my Calendar Table still works). 

Since I get updates of that csv monthly, I cannot do some sort of search an replace of the 22022 since it will change in every update. 

Thanks for your suggestions, Michaela

[Anonymous]

Thanks John,

I am not as experienced in Power Query now I struggle with where to put the variables, sorry.

My advanced editor looks like this now:

let
Source = Csv.Document(Parameter1,[Delimiter=";", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
Custom1 = Table.AddColumn(Source, Source{0}[Column1], each Source{0}[Column2]),
Custom2 = Table.RenameColumns(#"Custom1", { ColVal, ColName})
in
Custom2

[johnt75 · Accepted solution]

Replace all of that with

let
Source = Csv.Document(Parameter1,[Delimiter=";", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
ColName = Source{0}[Column1],
ColVal = Source{0}[Column2],
Custom1 = Table.AddColumn(Source, ColName, each ColVal)
in
Custom1

then do the remove rows and promote headers, then open Advanced Editor again and add

RenamedCols = Table.RenameColumns(#"Changed Type", { ColVal, ColName})

after the last step, just before the "in". Make sure to add a comma at the end of the previous line, and you'll need to change #"Changed Type" to the name of the previous step. Finally, replace the final line of the code with RenamedCols

[Anonymous]

Thanks John, that was really helpful! Have a great day 🙂

[Anonymous]

Hello @johnt75 ,

thanks for the reply, I am now at this point: 

The problem I have now is, that the header is now called 22022 and I cannot do replace since the numbers change. So the next thing I'd need to do is only rename the header of this column and I don't know how to do it without mentioning the 22022.

Greetings,

Michaela 

[johnt75]

Ah, yes. You can store the values in variables like

ColName = Source{0}[Column1],
ColVal = Source{0}[Column2],
AddedCol = Table.AddColumn( Source, ColName, each ColVal),

and then after you have deleted the top row and promoted the headers, you can add

RenamedCols = Table.RenameColumns(#"Changed Type", { ColVal, ColName})

[johnt75]

Add a step as

Table.AddColumn(Source, Source{0}[Column1], each Source{0}[Column2])

you can then remove the top row, promote the first row to headers and do any other transformations you need to