Hello I'm updating product registrations on a website via spreadsheet, but currently I do it manually, item by item
As there are more than 15 thousand products now
I would like to automate this process
Am I in doubt about the best way to do this based on my need?
(I'm using excel and powerquery, but I also have BI available)
I have this template spreadsheet where I make changes:
In this column called "Descrição Curta" I have cells with a set of text in html pattern, these are the cells that I need to update
In this image they are in the original pattern with line breaks (Enter) from excel itself, but to make it easier, I intend to do everything in full using only <br> to break the lines, because I don't know if the powerquery would recognize the breaks in normal lines (Enter) to use as a reference to divide cell text into parts
So what I would like to "Split" this text into parts and create "patterns", where only some parts of it will be replaced, according to the text found in the "Descrição" column where the cells with the product names are
This is the division I want to make to create the patterns:
This is the other column "Descrição" where the titles are:
1 - Title (This part should pull exactly the name that is in the "Descrição" column)
2 - Description (This will have a text pattern ready for each product theme (Flamengo, Corinthians, etc.), where I only need to replace one or two words based on the title of the description column, example: Text in title cell C2 is: " Copo Flamengo "
and The text for description in cell AQ2 is: <p> Description Festa Copo Corinthians description description. </p>
You would need to recognize the word "Festa" as a reference to automatically change based on C2 text to: <p> Description Festa Copo Flamengo description description. </p>
3 - They are characteristics based on product type (Toalha, Copo, Chapeu, Caixa) and the product type is always written in the description column text, so for exampleIf in cell C2 there is the word "Copo", this part would pull the text of characteristics for the "Copo" from a reference cell and place it here
4 - Warning - Just a standard text that will be in allWhat is the best way for me to create the automation of creating this?
Does Powerquery recognize line breaks? It would be more viable to write everything in full using <br> as a reference for breaks with the cell text being like this: <p> title </p> <br> <p> description </p> <br> <p> characteristics </ p> <br> <p> warning </p>
If I use <br> as a reference I can break the text of this cell into 4 exact parts, correct?
More for each of these 4 parts that I extract, it would have a different treatment as I informed above
So I would need to perform these actions that I need in those parts and then put them all back together in a single cell again
Can I do all of this on the bi / powerquery ? What commands can I use for the part I need to handle?/Being them:
5- Extract and split text of cell in parts based on (If possible) based on A: Count of Breaking Lines or B: 4 Words or C: <br> text in cell
6- For part 1 copy all the text in cell column "Descriçao" and put it after the first "<p>" text
7- For part 2 exchange existing text after a certain word and replace it with the same certain word in text of "Descriçao" column
8- For part 3 use a reference list in excel/bi and push the text based on one text word reference
9- For part 4 Just insert this text cell in all of cells
What commands i can use? Is better create all references and repeat all text in another excel sheet with 1 sheet for description texts each theme product, and other sheet with texts each type of product?
Any answer is of great help, sorry for the long text, I'm still getting to know the powerquery and I don't know exactly what it is capable of
Thanks for reading
