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.
Thanks You

Kana

Reply
Report

 

[GraH - Guido]

No need whatsoever for a formula in PQ.

1. Load both tables MasterData and Items (the names I gave them)
2. Unpivot the lookup columns in the MasterData table
3. Merge both tables on "attribute" (result of unpivot) and "item number", this to replace your vlookups
4. Expand the columns needed
5. replace null by "No" in the expanded columns
6. Load to Excel

Since you might have loads of data, it is advised to buffer the lookup table in the final query. See in the M-code advanced editor.

 

[AliGW]

Cross-posted: https://www.excelforum.com/excel-pr...rmula-language-or-formula-in-power-query.html

 

[Fluff13]

Also https://www.mrexcel.com/board/threa...a-language-or-formula-in-power-query.1125394/

 

[roykana]

No need whatsoever for a formula in PQ.

1. Load both tables MasterData and Items (the names I gave them)
2. Unpivot the lookup columns in the MasterData table
3. Merge both tables on "attribute" (result of unpivot) and "item number", this to replace your vlookups
4. Expand the columns needed
5. replace null by "No" in the expanded columns
6. Load to Excel

Since you might have loads of data, it is advised to buffer the lookup table in the final query. See in the M-code advanced editor.

Can you give me steps with screenshots in the word in the masterdata table and items.

Sorry if I've bothered you .

Thanks You

Roykana

 

[vletm]

roykana
Did You reread Forum Rules?
From here >> https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/

 

[roykana]

No need whatsoever for a formula in PQ.

1. Load both tables MasterData and Items (the names I gave them)
2. Unpivot the lookup columns in the MasterData table
3. Merge both tables on "attribute" (result of unpivot) and "item number", this to replace your vlookups
4. Expand the columns needed
5. replace null by "No" in the expanded columns
6. Load to Excel

Since you might have loads of data, it is advised to buffer the lookup table in the final query. See in the M-code advanced editor.

thank you very much

what buffer table can be used in the data source and for what purpose?

 

[vletm]

roykana
Seems that You skip Forum Rules for some reason.
Okay, I test this way ...
Open Conversation with me, if You would like to continue with this thread here.
Now, this thread is Closed.