Power Query M formula language or Formula in Power Query

[roykana]

Dear All Master ,

Please help me so that I do not use excel formulas such as in columns C, D, E, F that I marked yellow and I have a custom column in the power query namely PQ-ITEM NO NEW, PQ-GROUP SUPPLIER FIX, PQ-YEAR, PQ -DESCRIPTION so I want the results to use the power query formula or M language so that the results are the same as columns C, D, E, F I have yellow marking.
The reason I did not use the excel formula is because the record is 200000 lines so when it is refreshed it makes the formula calculation so that it makes it very long. For sheet master items do not need to be unpivoted so that they make many rows.
Please give me the solution.
My link file : FORMULA IN POWER QUERY.xlsx
Thanks You

Kana

 

[RoryA]

Please take a minute to reread the forum rules, especially as regards cross-posting in other forums, and then update this thread with the relevant links. Thanks.

 

[sandy666]

you can try with
IF ==> Conditional Column
VLOOKUP ==> Merge Queries
but if you want to use PQ , forget about worksheet functions
this is a completely different way of thinking

 

[roykana]

Please reply with my excel file. Because your solution it is not working.

Please give me best solution.


Thanks you

 

[roykana]

you can try with
IF ==> Conditional Column
VLOOKUP ==> Merge Queries
but if you want to use PQ , forget about worksheet functions
this is a completely different way of thinking

Please reply with my excel file. Because your solution it is not working.

Please give me best solution.


Thanks you

Reply
Report

 

[sandy666]

your solution it is not working.

this is general advice not ready-made solution

more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

 

[roykana]

this is general advice not ready-made solution

more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

Please help me with my excel file.

Thanks you

Roykana

 

[sandy666]

you haven't even read a single piece of information from the links
maybe someone else will give you ready-made solution

 

[roykana]

you haven't even read a single piece of information from the links
maybe someone else will give you ready-made solution

please help me solve the problem. because i am new to power query

 

[sandy666]

Add a conditional column (Power Query) - Microsoft Support

With Power Query, you can add a conditional column to your query. You can define IF-THEN-ELSE conditions in your query. When the conditions are fulfilled, the conditional column will automatically display the values that you specified.

support.office.com

Merge queries (Power Query) - Microsoft Support

A merge query creates a new query from two existing queries. One query result contains all columns from a primary table, with one column serving as a single column containing a navigation link to a related table.

support.office.com