[3주차] 파워쿼리 기초, 4주 완성 챌린지_스터디 노트 + 원드라이브 경로 관련 여쭙고 싶은 사항이 있습니다!

작성자
luwak0718
작성일
2023-11-09 15:48
조회
115

• 현업에서 파워쿼리를 사용하다보면 종종 마주하게되는 아래 오류의 발생 원인과 해결 방법을 간단히 작성합니다.

➡ 대부분 '변경된 유형' 단계에서 발생함. 머릿글 승격, 테이블 확장 등 구조를 바꾸는 순간 자동으로 데이터 형식을 맞추는 작업이 들어가는데 바뀌기 전 구조를 기준으로 쿼리가 작성되므로(실습 예시의 경우 하위 행의 머릿글 승격으로 인해 참조할 기존의 열 이름 'Column1'을 찾을 수 없게 됨) 오류가 발생한 단계를 지워주고 변환 탭의 '데이터 형식 검색' 기능을 이용해 데이터 형식을 맞춤
-----

• 파워쿼리 데이터에서 {}(중괄호)로 '행'을 선택하고, [](대괄호)로 '열'을 선택할 수 있습니다. Excel.CurrentWorkbook 으로 구한 현재 통합문서의 표 목록에서, 오른쪽과 같이 쿼리를 작성할 경우 어떻게 동작하나요? 동작 원리를 간단히 기술합니다.

➡ Excel.CurrentWorkbook(){0}[Content]{0}
Excel.CurrentWorkbook(): 현재의 통합문서 표 목록을 선택
{0}: 표 목록의 첫번째 행(표)을 선택
[Content]: 첫번째 행의 'Content' 열을 선택
{0}: 'Content' 열에 있는 테이블 데이터 중 첫번째 행인 '제품' - '망고'를 선택
-----

• 아래 그림과 같이 쿼리를 작성하면, 현재 실행중인 통합문서의 폴더 경로를 동적으로 받아와 실시간으로 쿼리를 업데이트 할 수 있습니다. 오른쪽에 작성된 쿼리의 동작 원리를 간단하게 정리합니다.

➡ 취합용 쿼리가 작성된 엑셀파일과 같은 폴더 경로에 취합 파일들이 있는 폴더가 있다면 해당 폴더의 경로를 동적으로 받아올 수 있음.
1. 현재 통합문서의 폴더 경로 반환 수식인 '=LEFT(CELL("filename",셀주소),FIND("\[",CELL("filename",셀주소)))'을 사용한 경로 데이터 열(예시에서 'CurrentPath' 열로 명명)과,
2. 여기에 해당 취합 파일들이 있는 폴더명('AccountHistory') 을 합친 데이터 열(앞선 수식에 &"AccountHistory"를 붙인 데이터, 'TargetPath' 열로 명명)을 작성하여 폴더 경로를 실시간으로 가져오고,
작성된 데이터가 포함된 범위를 표 변환한 후 표 이름을 지정(예시에서 'tblPath'로 명명)
3. 표에 대해 파워쿼리를 실행하고 데이터를 확인('변경된 유형' 단계가 있을 경우, 제거해줌)
Excel.CurrentWorkbook(){[Name="tblPath"]}[Content][TargetPath]{0}
Excel.CurrentWorkbook(): 현재의 통합문서 표 목록을 선택
{[Name="tblPath"]}: 표 목록 중 'tblPath'라는 이름의 표를 선택
[Content]: 'tblPath' 표의 'Content' 열(실제 표 데이터)을 선택하고
[TargetPath]{0}: 'TargetPath' 열의 내용을 가져오고 경로 데이터가 있는 첫번째 행을 선택함
* Excel.CurrentWorkbook(){[Name="tblPath"]}[Content]{0}[TargetPath] 또한 같은 데이터를 선택하므로 작성 순서에 크게 신경쓰지 말고 선택되는 데이터를 하나씩 확인하면서 작성할 것
4. 경로 데이터를 선택할 수 있는 쿼리(Excel.CurrentWorkbook(){[Name="tblPath"]}[Content][TargetPath]{0})를 홈 - 고급 편집기(적용된 단계들이 실제로 동작되는 코드들을 확인 가능)에서 변수로 지정해준 뒤(예시에서 'Path'로 명명) 원본으로 불러올 파일 경로를 변수 'Path'로 바꿔주면 동적 경로 데이터 취합이 완료됨
* 원본 단계에서 'tblPath'를 바로 사용하는 식으로 다른 쿼리나 단계를 직접 참조할 수는 없으므로 주의
* 취합 폴더명이 바뀌는 경우 'tblPath' 표에 폴더명 데이터가 들어있는 '하위폴더' 열을 만든 뒤 2번의 &"AccountHistory" 부분을 &[@하위폴더]로 바꿔주면 '하위폴더' 열의 데이터만 변환해도 최종 취합폴더 경로를 바꿔줄 수 있음
-----
* 원드라이브 폴더 내에 있는 파일 경로를 불러올 때 웹경로로 자동 변환되어 '=LEFT(CELL("filename",셀주소),FIND("\[",CELL("filename",셀주소)))' 작성 시 '#VALUE'로 표시되는 문제에 대해 클라우드 웹경로 데이터가 \이 아닌 /로 경로를 구분하기 때문인 것 같아 수식의 '\[' 부분을 '/['로 고쳐서 '=LEFT(CELL("filename",셀주소),FIND("/[",CELL("filename",셀주소)))'로 클라우드 웹경로 데이터를 불러오는 것까진 성공했습니다
그러나 파워쿼리를 이용해 해당 웹경로 데이터를 Path 변수로 동적 제공을 하면 오류가 발생합니다
혹시 클라우드 웹경로 데이터를 파워쿼리에 활용할 수 있거나 해당 데이터를 엑셀 함수 등으로 로컬 경로 데이터로 변환하는 방법은 없을까요?
-----
직접 코드를 짜는 건 엄두도 못내고 오빠두님 포함 다른 분들이 만들어주신 VBA 이용 함수나 추가 기능으로만 엑셀의 심화 기능들을 활용 중인데 시간이 많이 걸리겠지만 따라갈 수 있을 정도로 난이도를 조절해주셔서 그나마 조금씩 따라가고 있습니다 정말 감사드려요ㅜㅜ
신고
스크랩
공유
luwak0718
회원등급 : 9레벨
포인트 : 685 EP
게시글
전체 2
  • 2023-11-09 17:16

    @luwak0718 님 안녕하세요. 오빠두엑셀 전진권입니다.

    원드라이브 설정은 현재 확인중에 있습니다.

    예전에는 원드라이브의 Office 파일을 폴더경로 or 웹경로로 선택하는 설정이 있었으나, 현재는 이 설정이 사라져서 해결방법을 확인하고 있습니다. 방법을 찾는대로 공유해드리겠습니다.

    감사합니다.

    • 2023-11-09 17:50

      @오빠두엑셀 님 답변해주셔서 정말 감사합니다!! (__) 제가 원드라이브를 쓰는데 역시 쓰기 너무 어려운 것 같습니다😵

      —-

      원드라이브 폴더 내 경로에서 쓰시고자 하는 분들은 임시적인 방법으로,
      가져온 웹경로 데이터의 앞부분을 로컬경로로 바꿔 파워쿼리에서 인식하게 만들 수 있습니다

      원드라이브 웹경로의 상위 폴더 부분 텍스트 길이가 모든 사용자분에게 동일한 점을 이용해서
      (웹경로의 'https://d.docs.live.net/~~~~/' 에서 ~~~~ 부분은 텍스트 16자로 된 사용자 cid 코드입니다. 따라서 총 41자로 모든 분께 동일합니다) 해당 부분만 로컬 경로로 바꿔주도록

      =LEFT(CELL("filename",A2),FIND("\[",CELL("filename",A2)))

      수식을

      =SUBSTITUTE("C:\Users\사용자명\OneDrive\"&MID(LEFT(CELL("filename",A2),FIND("/[",CELL("filename",A2))),42,LEN(LEFT(CELL("filename",A2),FIND("/[",CELL("filename",A2))))),"/","\")

      형태로 MID 함수와 SUBSTITUTE 함수(웹경로의 "/"를 "\"로 바꿔줘야됩니다)를 써서 웹경로의 뒷부분만 가져오도록 바꿔주시면 일단 사용이 가능할 것 같습니다!
      ...만 식이 다소 복잡해지므로 해결방법을 알려주실 때까지 되도록 로컬 드라이브로 취합 폴더와 파일을 옮기신 다음 기존의 수식을 쓰시는 걸 추천드립니다😥

« 파워쿼리 기초, 4주 완성 챌린지(3주차)
파워쿼리 3주차 학습노트 »
목록보기
전체 2,664
번호 제목 작성자 작성일 추천 조회
알림
[📚10만부 출간 기념] 「진짜쓰는 실무엑셀」 한정판 리커버 + 8시간 로드맵 특별 부록
오빠두엑셀 | 07:19 | 추천 7 | 조회 326
오빠두엑셀 07:19 7 326
64692
New 진짜쓰는 실무엑셀 8시간 로드맵
김선호 | 2023.12.08 | 추천 2 | 조회 28
김선호 2023.12.08 2 28
64670
New 엑셀 기초 4시간 완성 - 원데이 챌린지
영철공주 | 2023.12.07 | 추천 0 | 조회 20
영철공주 2023.12.07 - 20
64636
New 액셀 기초 4시간 완성 - 원데이 클래스
민락남 | 2023.12.07 | 추천 0 | 조회 38
민락남 2023.12.07 - 38
64159
엑셀 기초 4시간 완성 - 원데이 챌린지
미소 | 2023.11.24 | 추천 0 | 조회 115
미소 2023.11.24 - 115
64119
기초 입문 강의 듣는 중 막히는 게 생깁니다.
에르 | 2023.11.23 | 추천 0 | 조회 95
에르 2023.11.23 - 95
64032
엑셀 기초, 4시간 완성 - 원데이 챌린지 1주차
김은하 | 2023.11.21 | 추천 0 | 조회 167
김은하 2023.11.21 - 167
63966
엑셀 기초, 4시간 완성 - 원데이 챌린지 스터디 첨부파일
chan**** | 2023.11.19 | 추천 0 | 조회 114
chan**** 2023.11.19 - 114
63964
엑셀 기초, 4시간 완성 - 원데이 챌린지
누엥 | 2023.11.19 | 추천 0 | 조회 112
누엥 2023.11.19 - 112
63961
[엑셀 기초] 4시간 완성! - 원데이 챌린지 첨부파일
noah**** | 2023.11.19 | 추천 1 | 조회 123
noah**** 2023.11.19 1 123
63942
[파워쿼리 기초] 4주완성 챌린지_4주차
연자매 | 2023.11.18 | 추천 0 | 조회 104
연자매 2023.11.18 - 104
63939
파워쿼리 기초 4주차 첨부파일
DoraDora | 2023.11.17 | 추천 0 | 조회 113
DoraDora 2023.11.17 - 113
63938
파워쿼리 기초, 4주 완성 챌린지 4주차
39 | 2023.11.17 | 추천 0 | 조회 109
39 2023.11.17 - 109
63937
[파워쿼리기조 4주완성] 4주차
사육사 | 2023.11.17 | 추천 0 | 조회 101
사육사 2023.11.17 - 101
63936
파워쿼리 기초, 4주 특별 챌린지_4주차 첨부파일
달빛술사 | 2023.11.17 | 추천 0 | 조회 94
달빛술사 2023.11.17 - 94
63935
파워쿼리 실습 4주차 강의
홍s | 2023.11.17 | 추천 0 | 조회 88
홍s 2023.11.17 - 88
63922
파워쿼리 기초 완성 4주 챌린지 4주차
ㅇㅇ나두 | 2023.11.17 | 추천 0 | 조회 108
ㅇㅇ나두 2023.11.17 - 108
63920
[파워쿼리기조 4주완성] 4주차
Indi | 2023.11.17 | 추천 0 | 조회 94
Indi 2023.11.17 - 94
63917
파워쿼리 기초, 4주 완성 챌린지 4주차
한상 | 2023.11.17 | 추천 0 | 조회 92
한상 2023.11.17 - 92
63911
[엑셀 기초] 4시간 완성! - 원데이 챌린지 완료 첨부파일
해븐스 | 2023.11.17 | 추천 0 | 조회 112
해븐스 2023.11.17 - 112
63907
파워쿼리 기초, 4주 완성 챌린지 4주차
레지나 | 2023.11.17 | 추천 0 | 조회 101
레지나 2023.11.17 - 101