학습노트
파워쿼리_2주차 챌린지&스터디 노트
유 🔥 실습 예제과 함께 공부하는 모습을 함께 올려보세요!
(마우스 드래그 & 스크린샷+붙여넣기로 편리하게 그림을 넣을 수 있습니다)
① Forder.Files ("폴더경로") :
폴더 안의 파일 목록을 반환하는 M(mapping)함수 입니다.
'데이터탭'의 '테이터 가져오기'에서 '파일'->'폴더에서'를 선택한 후 취합하고자 하는 데이터가 있는 폴더(예: c 거래내역 보고서)를 선택하고 '테이터 변환' 버튼을 클릭하면 파워쿼리 편집기에서 처음 원본에 반환되는 폴더 안의 파일 목록이 바로 뜨게 됩니다.
왼쪽 contentd의 Binary에 취합하려는 엑셀 파일 시트 정보가 있게 됩니다.
② Excel.Workbook (엑셀파일) :
취합하고자 하는 데이터의 파일 목록, 시트 등을 반환하는 M함수입니다.
원본 단계에서는 파일 목록이 불러와지고, 취합하고자 하는 엑셀 파일은 content에 있어서 다른 열들은 먼저 제거합니다. 이때 contet는 각각의 엑셀 파일이고 취합하고자 하는 엑셀 파일 데이터(시트)는 Binary에 있기 때문에 그 정보를 불러오기 위해 '열추가' 탭의 '사용자 지정 열'에서 사용하는 M함수가 "Excel.Workbook (엑셀파일)"입니다.
이때 주의할 점은 Excel의 E와 Workbook의 W는 대문자로 써야 합니다.
③ Table.Skip (테이블, 개수 or 조건) :
테이블에서 상위 n개 행 또는 조건을 만족하는 행을 제거하는 M함수입니다.
취합하고자 하는 파일의 데이터가 각 시트의 A1열 부터 시작하지 않고 불필요한 서식들이 있다면 그 불필요한 머리글 행들을 먼저 지워주고 병합할때 사용하는 함수로, 이때도 Table의 T와 Skip의 S는 대문자로 써줘야 합니다.
④ Table.PromoteHeaders (테이블):
Table의 데이터의 첫 행을 머리글로 승격하는 M함수입니다.
머리글을 승격하지 않고 데이터를 확장, 병합시키면 머리글이 중복되어 나온다는 문제와 각 엑셀 파일마다 머리글의 순서가 다를 경우 제대로 파일 병합이 되지 않는다는 문제가 생깁니다.
파일을 병합하고 취합할 때 머리글의 순서가 아니라 머리글 필드를 참고해서 취합하기 때문에 취합하려는 데이터의 머리글을 승격시키지 않으면 각tavble의 기본 설정된 머리글(column1,,,)을 따라 제대로 데이터가 취합되지 않을 수 있습니다.
이 함수에서도 Table의 T와 PromoteHeaders의 P와 H를 대문자로 써줘야 합니다.
M함수에 행을 선택할 때는 중괄호는{}를 사용하고, 필드(열)을 선택할 때는 대괄호[]를 사용합니다.
이때, M함수는 배열이 0부터 시작함을 주의해야 합니다.
만약 위 예제에서 '제주도'의 '가격'이 보고 싶다면,
제주도의 경우 3번째 행에 있고 가격 필드를 보고 싶다는 거니까,
"=테이블{2}[가격]"이라고 해도 되지만
문제의 "지역이 제주도인 제품의 가격"이란 조건으로 출력하기 위해서는 지역필드를 행에 넣어야 합니다. 그래서
"=테이블{[지역="제주도"]}[가격]이라고 수식을 넣어야 합니다.
이렇게 중괄호{}와 대괄호[]를 사용해서 조건에 맞는 데이터를 출력할 수 있습니다.
(참고-비밀노트 시트5의 예제표)
2주 챌린지 동안 파워쿼리 강의를 들으면서 새로운 엑셀 세계를 접해서 놀라웠습니다.
1주차 강의 때처럼 그동안 잘 만들어진 피벗된 표를 만들고 또 다시 그 피벗된 표로 데이터를 병합하고 취합했던 시간들이 불필요했음을 깨달았고, 데이터를 정리하고 병합하고 취합해서 파워쿼리된 데이터를 가지고 피벗테이블을 만들고 챠트를 만드는게 실무에서 굉장히 유용하다는 것을 배웠습니다.
특히 폴더안의 여러 엑셀파일 데이터를 취합할 때에 굳이 각각의 파일들을 표로 바꾸지 않아도 되기에 파워쿼리는 매우 편리한 기능이라고 생각합니다.
또한 파워쿼리 편집기에서 편집된 과정을 볼 수 있어서 조건을 추가하거나 수정할 때도 유용하다고 생각합니다.
그리고 이번 시간에 배운 M함수를 통해 머리글을 승격(Table.PromoteHeaders)하고, 행을 스킵(Table.Skip)하고, 엑셀 파일의 데이터를 쉽게 반환(Excel.Workbook)하는 것을 배우고 나니 아직은 어렵지만 더 연습하고 활용해 보고 싶습니다.
마지막으로 동적으로 폴더 경로를 받아와서 파일을 취합하는 자동화 서식을 만드는 걸 배웠는데 아직 이 부분은 어렵지만 실무에서 굉장히 유용한 기능이라고 생각합니다.
"=LEFT(CELL("filename",A1),FIND("\[",CELL("filename",A1))))" 수식과 파워쿼리의 '고급 편집기'를 사용하면, 실시간으로 파워쿼리에 사용된 자료의 폴더 경로가 반환되고 폴더의 이름이 바뀌어도, 폴더 안의 취합된 데이터가 하위폴더로 묶여도 데이터가 있는 폴더에 취합파일(쿼리파일)이 함께 있다면 경로가 자동으로 업데이트되어서 오류 없이 데이터가 병합.취합됩니다.
<<스터디 노트>>
(마우스 드래그 & 스크린샷+붙여넣기로 편리하게 그림을 넣을 수 있습니다)
① Forder.Files ("폴더경로") :
폴더 안의 파일 목록을 반환하는 M(mapping)함수 입니다.
'데이터탭'의 '테이터 가져오기'에서 '파일'->'폴더에서'를 선택한 후 취합하고자 하는 데이터가 있는 폴더(예: c 거래내역 보고서)를 선택하고 '테이터 변환' 버튼을 클릭하면 파워쿼리 편집기에서 처음 원본에 반환되는 폴더 안의 파일 목록이 바로 뜨게 됩니다.
왼쪽 contentd의 Binary에 취합하려는 엑셀 파일 시트 정보가 있게 됩니다.
② Excel.Workbook (엑셀파일) :
취합하고자 하는 데이터의 파일 목록, 시트 등을 반환하는 M함수입니다.
원본 단계에서는 파일 목록이 불러와지고, 취합하고자 하는 엑셀 파일은 content에 있어서 다른 열들은 먼저 제거합니다. 이때 contet는 각각의 엑셀 파일이고 취합하고자 하는 엑셀 파일 데이터(시트)는 Binary에 있기 때문에 그 정보를 불러오기 위해 '열추가' 탭의 '사용자 지정 열'에서 사용하는 M함수가 "Excel.Workbook (엑셀파일)"입니다.
이때 주의할 점은 Excel의 E와 Workbook의 W는 대문자로 써야 합니다.
③ Table.Skip (테이블, 개수 or 조건) :
테이블에서 상위 n개 행 또는 조건을 만족하는 행을 제거하는 M함수입니다.
취합하고자 하는 파일의 데이터가 각 시트의 A1열 부터 시작하지 않고 불필요한 서식들이 있다면 그 불필요한 머리글 행들을 먼저 지워주고 병합할때 사용하는 함수로, 이때도 Table의 T와 Skip의 S는 대문자로 써줘야 합니다.
④ Table.PromoteHeaders (테이블):
Table의 데이터의 첫 행을 머리글로 승격하는 M함수입니다.
머리글을 승격하지 않고 데이터를 확장, 병합시키면 머리글이 중복되어 나온다는 문제와 각 엑셀 파일마다 머리글의 순서가 다를 경우 제대로 파일 병합이 되지 않는다는 문제가 생깁니다.
파일을 병합하고 취합할 때 머리글의 순서가 아니라 머리글 필드를 참고해서 취합하기 때문에 취합하려는 데이터의 머리글을 승격시키지 않으면 각tavble의 기본 설정된 머리글(column1,,,)을 따라 제대로 데이터가 취합되지 않을 수 있습니다.
이 함수에서도 Table의 T와 PromoteHeaders의 P와 H를 대문자로 써줘야 합니다.
M함수에 행을 선택할 때는 중괄호는{}를 사용하고, 필드(열)을 선택할 때는 대괄호[]를 사용합니다.
이때, M함수는 배열이 0부터 시작함을 주의해야 합니다.
만약 위 예제에서 '제주도'의 '가격'이 보고 싶다면,
제주도의 경우 3번째 행에 있고 가격 필드를 보고 싶다는 거니까,
"=테이블{2}[가격]"이라고 해도 되지만
문제의 "지역이 제주도인 제품의 가격"이란 조건으로 출력하기 위해서는 지역필드를 행에 넣어야 합니다. 그래서
"=테이블{[지역="제주도"]}[가격]이라고 수식을 넣어야 합니다.
이렇게 중괄호{}와 대괄호[]를 사용해서 조건에 맞는 데이터를 출력할 수 있습니다.
(참고-비밀노트 시트5의 예제표)
2주 챌린지 동안 파워쿼리 강의를 들으면서 새로운 엑셀 세계를 접해서 놀라웠습니다.
1주차 강의 때처럼 그동안 잘 만들어진 피벗된 표를 만들고 또 다시 그 피벗된 표로 데이터를 병합하고 취합했던 시간들이 불필요했음을 깨달았고, 데이터를 정리하고 병합하고 취합해서 파워쿼리된 데이터를 가지고 피벗테이블을 만들고 챠트를 만드는게 실무에서 굉장히 유용하다는 것을 배웠습니다.
특히 폴더안의 여러 엑셀파일 데이터를 취합할 때에 굳이 각각의 파일들을 표로 바꾸지 않아도 되기에 파워쿼리는 매우 편리한 기능이라고 생각합니다.
또한 파워쿼리 편집기에서 편집된 과정을 볼 수 있어서 조건을 추가하거나 수정할 때도 유용하다고 생각합니다.
그리고 이번 시간에 배운 M함수를 통해 머리글을 승격(Table.PromoteHeaders)하고, 행을 스킵(Table.Skip)하고, 엑셀 파일의 데이터를 쉽게 반환(Excel.Workbook)하는 것을 배우고 나니 아직은 어렵지만 더 연습하고 활용해 보고 싶습니다.
마지막으로 동적으로 폴더 경로를 받아와서 파일을 취합하는 자동화 서식을 만드는 걸 배웠는데 아직 이 부분은 어렵지만 실무에서 굉장히 유용한 기능이라고 생각합니다.
"=LEFT(CELL("filename",A1),FIND("\[",CELL("filename",A1))))" 수식과 파워쿼리의 '고급 편집기'를 사용하면, 실시간으로 파워쿼리에 사용된 자료의 폴더 경로가 반환되고 폴더의 이름이 바뀌어도, 폴더 안의 취합된 데이터가 하위폴더로 묶여도 데이터가 있는 폴더에 취합파일(쿼리파일)이 함께 있다면 경로가 자동으로 업데이트되어서 오류 없이 데이터가 병합.취합됩니다.
<<스터디 노트>>
g
사
댓글 0