환율/시장지표 모니터링과
비즈니스 영향 분석

let
    // ==============================
    // 설정값
    // ==============================
    ApiKey = "받은APIKey",

    StartDate = #date(2026, 9, 1),
    EndDate   = #date(2026, 9, 30),

    TargetCurrencies = {
        "USD",
        "EUR",
        "JPY(100)",
        "CNH"
    },

    BaseUrl =
        "https://oapi.koreaexim.go.kr/site/program/financial/exchangeJSON",

    // ==============================
    // 날짜 목록 생성
    // ==============================
    DateList =
        List.Dates(
            StartDate,
            Duration.Days(EndDate - StartDate) + 1,
            #duration(1, 0, 0, 0)
        ),

    // ==============================
    // 날짜별 API 호출 함수
    // ==============================
    GetExchangeRate = (RequestDate as date) as table =>
        let
            SearchDate =
                Date.ToText(RequestDate, "yyyyMMdd"),

            JsonData =
                try
                    Json.Document(
                        Web.Contents(
                            BaseUrl,
                            [
                                Query = [
                                    authkey = ApiKey,
                                    searchdate = SearchDate,
                                    data = "AP01"
                                ],
                                Timeout = #duration(0, 0, 30, 0)
                            ]
                        )
                    )
                otherwise
                    null,

            // 주말/공휴일 등 데이터가 없는 경우 빈 테이블 반환
            Result =
                if
                    JsonData = null
                    or not Value.Is(JsonData, type list)
                    or List.Count(JsonData) = 0
                then
                    #table(
                        {
                            "date",
                            "cur_unit",
                            "cur_nm",
                            "deal_bas_r"
                        },
                        {}
                    )
                else
                    let
                        Source =
                            Table.FromRecords(JsonData),

                        // 요청 통화만 필터
                        Filtered =
                            Table.SelectRows(
                                Source,
                                each
                                    List.Contains(
                                        TargetCurrencies,
                                        [cur_unit]
                                    )
                            ),

                        // 필요한 컬럼만 선택
                        Selected =
                            Table.SelectColumns(
                                Filtered,
                                {
                                    "cur_unit",
                                    "cur_nm",
                                    "deal_bas_r"
                                },
                                MissingField.UseNull
                            ),

                        // 조회 기준일 추가
                        AddedDate =
                            Table.AddColumn(
                                Selected,
                                "date",
                                each RequestDate,
                                type date
                            ),

                        Reordered =
                            Table.ReorderColumns(
                                AddedDate,
                                {
                                    "date",
                                    "cur_unit",
                                    "cur_nm",
                                    "deal_bas_r"
                                }
                            ),

                        // "1,329.90" → 1329.90 숫자로 변환
                        Converted =
                            Table.TransformColumns(
                                Reordered,
                                {
                                    {
                                        "deal_bas_r",
                                        each
                                            try
                                                Number.FromText(
                                                    Text.Replace(
                                                        Text.From(_),
                                                        ",",
                                                        ""
                                                    ),
                                                    "en-US"
                                                )
                                            otherwise
                                                null,
                                        type number
                                    }
                                }
                            )
                    in
                        Converted
        in
            Result,

    // ==============================
    // 전체 날짜 API 호출
    // ==============================
    DailyTables =
        List.Transform(
            DateList,
            each GetExchangeRate(_)
        ),

    Combined =
        Table.Combine(DailyTables),

    // ==============================
    // 정렬
    // ==============================
    Sorted =
        Table.Sort(
            Combined,
            {
                {"date", Order.Ascending},
                {"cur_unit", Order.Ascending}
            }
        )
in
    Sorted

if
    JsonData = null
    or not Value.Is(JsonData, type list)
    or List.Count(JsonData) = 0
then
    #table(...)

    Combined =
        Table.Combine(DailyTables),

    Pivoted =
        Table.Pivot(
            Combined,
            List.Distinct(Combined[cur_unit]),
            "cur_unit",
            "deal_bas_r",
            List.First
        ),

    RemovedCurrencyName =
        Table.RemoveColumns(
            Pivoted,
            {"cur_nm"}
        ),

    Sorted =
        Table.Sort(
            RemovedCurrencyName,
            {
                {"date", Order.Ascending}
            }
        )
in
    Sorted

let
    ApiKey = "받은APIKey",

    StartDate = #date(2026, 9, 1),
    EndDate   = #date(2026, 9, 30),

    TargetCurrencies = {
        "USD",
        "EUR",
        "JPY(100)",
        "CNH"
    },

    BaseUrl =
        "https://oapi.koreaexim.go.kr/site/program/financial/exchangeJSON",

    DateList =
        List.Dates(
            StartDate,
            Duration.Days(EndDate - StartDate) + 1,
            #duration(1, 0, 0, 0)
        ),

    GetExchangeRate = (RequestDate as date) as table =>
        let
            SearchDate =
                Date.ToText(RequestDate, "yyyyMMdd"),

            JsonData =
                try
                    Json.Document(
                        Web.Contents(
                            BaseUrl,
                            [
                                Query = [
                                    authkey = ApiKey,
                                    searchdate = SearchDate,
                                    data = "AP01"
                                ]
                            ]
                        )
                    )
                otherwise
                    null,

            Result =
                if
                    JsonData = null
                    or not Value.Is(JsonData, type list)
                    or List.Count(JsonData) = 0
                then
                    #table(
                        {"date", "cur_unit", "deal_bas_r"},
                        {}
                    )
                else
                    let
                        T =
                            Table.FromRecords(JsonData),

                        Filtered =
                            Table.SelectRows(
                                T,
                                each
                                    List.Contains(
                                        TargetCurrencies,
                                        [cur_unit]
                                    )
                            ),

                        Selected =
                            Table.SelectColumns(
                                Filtered,
                                {
                                    "cur_unit",
                                    "deal_bas_r"
                                }
                            ),

                        AddedDate =
                            Table.AddColumn(
                                Selected,
                                "date",
                                each RequestDate,
                                type date
                            ),

                        Converted =
                            Table.TransformColumns(
                                AddedDate,
                                {
                                    {
                                        "deal_bas_r",
                                        each
                                            try
                                                Number.FromText(
                                                    Text.Replace(
                                                        Text.From(_),
                                                        ",",
                                                        ""
                                                    ),
                                                    "en-US"
                                                )
                                            otherwise
                                                null,
                                        type number
                                    }
                                }
                            )
                    in
                        Converted
        in
            Result,

    DailyTables =
        List.Transform(
            DateList,
            each GetExchangeRate(_)
        ),

    Combined =
        Table.Combine(DailyTables),

    Pivoted =
        Table.Pivot(
            Combined,
            TargetCurrencies,
            "cur_unit",
            "deal_bas_r",
            List.First
        ),

    Sorted =
        Table.Sort(
            Pivoted,
            {
                {"date", Order.Ascending}
            }
        )
in
    Sorted