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

 

[sandy666]

good job
have a nice day

 

[roykana]

can i download the excel file that you sent ?

Lines 9 through 10 have not been filled

 

[roykana]

Lines 9 through 10 have not been filled

I want the results to be the same as in the data sheet that I marked in yellow

 

[sandy666]

something went wrong, wait a moment.........

edit: try now s666-PQ-FORMULA IN POWER QUERY-v2.xlsx

 

[roykana]

something went wrong, wait a moment.........

edit: try now s666-PQ-FORMULA IN POWER QUERY-v2.xlsx

As per the decryption I posted, I don't want to use unpivot in the master item because the master item has 30000 new data. If I use unpivot, there will be more lines and the results for the item named jasaservice will not produce results. In the data_test qUERY that you create, just add 4 columns and there is no need for a new query, merge1

 

[sandy666]

I show you the way you can use
Get it or forget it

 

[roykana]

I show you the way you can use
Get it or forget it

I thank you but I need another solution