Remove text in one specific cell in Power Query Editor

[Anonymous]

Hey fellow Power BI:ers

I am very new to Power BI and am currently editing data in the Power Query editor to "wash it" prior to inserting it into Power BI. As seen in screenshot below I wish to empty/delete/nullify the text stored specificly in cellA2 (marked in yellow). Does anyone have any good suggestion on how to do this?



All answers are appreciated

Best wishes

/Sammy

[ImkeF]

Hi @Anonymous ,
to do this reliably, one has to know the logic behind the cell detection.
If it is always the 2nd row in column week, you could add an index colum (starting at 0) and then add a custom column that check whether the value in the index column is 1: Then return null otherwise return the value from the Week-column. 
Then delete the week-column and rename the newly created column to Week.

[Anonymous]

Hey, thanks for reaching out @ImkeF 

The logic is basically that I would want to find the first column and simply rename it to "Week".

When using the renaming function it is not dynamic as it simply looks for the column called "202252" and renames it to "Week". Please see attached screenshot.

In reality I will upload data with other dates such as 202245 etc and then it would not work... Can I somehow replace "202252" in the code above with first column number or so?

Thanks
/Sammy

[ImkeF · Accepted solution]

Hi @Anonymous ,
thanks, that explanation would have made an excellent initial thread 😉

To rename the first column dynamically, you can use this formula:

Table.RenameColumns( #"Promoted Headers", {Table.ColumnNames(#"Promoted Headers"){0}, "Week"})

So instead of hardcoding the column name, you grab its value by fetching the tables column names as list and and then grab the first element from the list {0} - with 0 as a positional index because the M-language is zero based.