Help us understand the problem. What is going on with this article?

[PowerQuery]営業日数を取得する

More than 1 year has passed since last update.

ExcelのNETWORKDAYS.INTL関数で「週末」に"0000000"(=週末無し)を指定したときと同じ挙動になるPowerQueryの関数をメモ

前提

休日一覧のテーブルをPowerQueryに取り込んでおく。
見出しを「休日」とする。
休日一覧.png

関数

(開始日 as date, 終了日 as date, optional 休日一覧 as table) as number =>
let
  Lst休日一覧 = if 休日一覧 = null then {} else Table.Column(休日一覧,"休日"),
  NumLst休日一覧 = List.Transform(Lst休日一覧 ,each Number.From(_)),
  NumLst指定期間 = if 開始日<終了日 then {Number.From(開始日)..Number.From(終了日)} else {Number.From(終了日)..Number.From(開始日)},
  NumLst差分  = List.Difference(NumLst指定期間, NumLst休日一覧),
  営業日数 = if 開始日<終了日 then List.Count(NumLst差分) else List.Count(NumLst差分)*(-1)
in
  営業日数

2018/07/24追記 ifを使わないバージョンを作ろうと思ったが、休日一覧が指定されないときにTable.Columnでこけるので、そっとoptionalを外して今回は逃げることにした↓

(開始日 as date, 終了日 as date, 休日一覧 as table) as number =>
let
  指定期間 = List.Generate( () => List.Min({開始日,終了日}), each _ <= List.Max({開始日,終了日}), each Date.AddDays(_,1)),
  営業日数 = List.Count( List.Difference( 指定期間, Table.Column(休日一覧,"休日") ) )
in
  営業日数

参考

Tech Net: Calculate Working Days using Power Query

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away