Hello everyone,
While using power query, I am looking for a formula that will insert text into the rows below its column, I can't figure out how the formula should be and I am working with two worksheets, one is for Project A and the other is for Project B.
I want the formula to check if there is any text on the next rows and display a custom text.
Can you help ?
Much obliged !
Last edited by Rastancu; 10-28-2019 at 07:51 AM.
Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.
Instructions (Please Read Carefully):
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired results are also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as they cause lots of problems.
Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thank you AliGW, document is now attached !
- forgot how to attach, apologies, now it's attached on the first post, thank you again !
No, it isn't ...
You have not shown us what you are trying to achieve - please manually mock up what you want and repost the workbook.
Is the new version ok now ?
Using PQ I've appended two tables, I've added to each table an extra custom column where I wish to insert a text formula to display a custom text so that in the appended table it will appear with both table's formula results.
I hope this covers what I need, it's the first time when I work with Power Query.
Not really!
What is the custom text you want to show? That's what you need to mock up.
Oh ! I understand now, I've changed it to "return text "cba", in table 1 and table 2, I need a formula that inserts text "abc" for table 1 and "cba" for table 2.
No, we aren't there yet. This is not what you really want to see, is it?
Excel 2016 (Windows) 32 bit
Column1 Column2 Column3 Column4 Column5 Column to insert Formula Column to insert formula2 A B C D E Return Text "ABC" Return Text "ABC" Return Text "ABC" Return Text "ABC" Return Text "ABC" Return Text "ABC" Return Text "ABC" Return Text "ABC" Return Text "ABC" Return Text "ABC" Return Text "ABC" Return text "cba" Return text "cba" Return text "cba" Return text "cba" Return text "cba" Return text "cba" Return text "cba" Return text "cba" Return text "cba" Return text "cba" Return text "cba" Return text "cba"
Sheet: Sheet1
I need to know EXACTLY what you are aiming for. Mock it up MANUALLY - never mind Power Query for now.
You are right, yes all on the same column, I believe now it's correct and on par.
I'm still not convinced you are telling us what we need to know.
Have a look at the attached - is it what you want?
Not quite, the results are indeed what I need but I need a formula that can give me those values, the formula needs to work in power query editor, I saw that they are a bit different than normal excel formulas.
Then in Table 1, I will use the formula to insert "ABC" and in Table 2 to insert "CBA".
Yes, but how is Excel meant to work out what you want??? What relevance do those text strings have in each table? What formula are you talking about?
You need to be completely clear: mock up the two source tables as you want them to be prior to merging.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks