SOLVED
Home

vlook up Power query

%3CLINGO-SUB%20id%3D%22lingo-sub-615295%22%20slang%3D%22en-US%22%3Evlook%20up%20Power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-615295%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20wish%20to%20create%20a%20personalized%20column%20in%20Power%20query%20for%20a%20data%20base.%20This%20column%20would%20be%20conditional%3A%20if%20the%20condition%20is%20true%2C%20you%20put%20a%20text%2C%20if%20it's%20not%2C%20you%20put%20the%20price%20related%20to%20the%20reference%2C%20which%20is%20in%20another%20worksheet%20of%20the%20file.%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20create%20this%20column%20with%20the%20advanced%20editor%20in%20Power%20Query%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20Nathan.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-615295%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-615340%22%20slang%3D%22en-US%22%3ERe%3A%20vlook%20up%20Power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-615340%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F344880%22%20target%3D%22_blank%22%3E%40Nathan0763%3C%2FA%3E%26nbsp%3B%2C%20perhaps%20first%20you%20need%20to%20merge%20you%20main%20table%20with%20another%20query%20with%20prices%20and%20extract%20prices.%20After%20that%20add%20custom%20column%20with%3C%2FP%3E%0A%3CP%3Eif%20%3CCONDITION%3E%20then%20%5BText%5D%20else%20%5BPrice%5D%3C%2FCONDITION%3E%3C%2FP%3E%0A%3CP%3EHowever%2C%20that's%20not%20a%20good%20idea%20to%20mix%20texts%20and%20numbers%20in%20one%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-615397%22%20slang%3D%22en-US%22%3ERe%3A%20vlook%20up%20Power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-615397%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20Thanks%20for%20your%20reply.%20I%20tried%20to%20merge%20the%20two%20worksheets%20but%20the%20merger%20is%20just%20creating%20a%20column%20of%20%22Tables%22.%3C%2FP%3E%3CP%3EYou%20were%20right%20about%20the%20format%20though%2C%20the%20condition%20returns%20either%200%2C%20or%20the%20price.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENathan.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-615425%22%20slang%3D%22en-US%22%3ERe%3A%20vlook%20up%20Power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-615425%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F344880%22%20target%3D%22_blank%22%3E%40Nathan0763%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENathan%2C%20yes%2C%20that%20will%20be%20column%20with%20tables.%20Click%20on%20the%20icon%20with%20arrows%20on%20top%20right%20of%20that%20column%20header%20and%20extract%20Price%20and%20other%20fields%20which%20necessary.%20If%20you%20build%20relationship%20correctly%20it%20return%20Price%20or%20null%20if%20no%20related%20price.%20You%20may%20keep%20null%20as%20it%20is%20or%20replace%20it%20on%20zero.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-615563%22%20slang%3D%22en-US%22%3ERe%3A%20vlook%20up%20Power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-615563%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BOkay%2C%20that's%20working%20perfectly%2C%20thank%20you%20very%20much%20!%3C%2FP%3E%3CP%3ENathan.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-615801%22%20slang%3D%22en-US%22%3ERe%3A%20vlook%20up%20Power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-615801%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F344880%22%20target%3D%22_blank%22%3E%40Nathan0763%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Nathan0763
New Contributor

Hello, I wish to create a personalized column in Power query for a data base. This column would be conditional: if the condition is true, you put a text, if it's not, you put the price related to the reference, which is in another worksheet of the file.

Is it possible to create this column with the advanced editor in Power Query?

 

Thank you, Nathan.

5 Replies

@Nathan0763 , perhaps first you need to merge you main table with another query with prices and extract prices. After that add custom column with

if <condition> then [Text] else [Price]

However, that's not a good idea to mix texts and numbers in one column.

@Sergei Baklan  Thanks for your reply. I tried to merge the two worksheets but the merger is just creating a column of "Tables".

You were right about the format though, the condition returns either 0, or the price.

 

Nathan.

Solution

@Nathan0763 

Nathan, yes, that will be column with tables. Click on the icon with arrows on top right of that column header and extract Price and other fields which necessary. If you build relationship correctly it return Price or null if no related price. You may keep null as it is or replace it on zero.

@Sergei Baklan Okay, that's working perfectly, thank you very much !

Nathan.

@Nathan0763 , you are welcome

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies