PQ 엑셀 셀 값을 파워쿼리에 연결하기

20210324 1410 
 
REF
[1] "stackoverflow.com/questions/26989279/how-can-i-reference-a-cells-value-in-powerquery"
[2] "www.myonlinetraininghub.com/excel-forum/power-query/convert-serial-number-to-date"
[3] "docs.microsoft.com/en-us/powerquery-m/datetime-date"
 
여기와 똑같은 결과를 ▶ https://weneedu.tistory.com/536 ▶ 사용자 지정함수 없이 테이블을 이용할 수도 있다.

00 Outline
Today should change as time goes.
Thus days to maturity of certain financial instruments should change as well.
Let's reflect this to power query.

00 Example file
 

zManual Excel PQ_How can I reference a cell's value in PowerQuery.xlsx

0.02MB

 
01 How to do, [1]
① Prepare data and table
- B6 => give name to "B6" as "zToday"
  (looks like need to 'name" to use data in powerquery)
- make table "A9:E12" as "Table1"
② Make power query from "Table 1"
③ (Making function) make new Query as below
 

 
▶ the code

= (zDate) => Excel.CurrentWorkbook(){[Name=zDate]}[Content]{0}[Column1]

④ Return to "Table1"  and add a colum as below
 

 
▶ code generated by above action

= Table.AddColumn(#"Changed Type", "Custom", each zGetDate("zToday"))

⑤ Return to excel and test if the parameter change will affect the derived table(A16:G19)
 
02 Some notes or remarks
① "(zDate) => " makes it a function
② "Text.From()" or "Number.ToText" can convert data type
 
↓ from [3]
③ converting date : DateTime.Date(#date(2010,12,31)
④ converting date tiem : DateTime.Date(#datetime(2010,12,31,11,56,02)
 
⑤ number of days from column1 to column2 (subtracting a date from anothe date)
= select column2 and (while press "Ctrl") click column1
= then select "Add Column > Date > Subtract Days" as below