SQLの世界では不等号結合がありますよね。Power Queryではどういう手法がいいんでしょう?
ツイートの画像と一緒にご覧ください。
実際のコード
テストテーブルのレコード数は減らしています。
マスタテーブル
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXo+ZROQNDIwMNA3MNQ3MIVzjPWNDZVidaKVnHCqMkRS5QwUejqhE4sqMyRVIBuf7JwJV2UCVAjk5JXm5MDtgssbwuWBHFN0u5Ysh1tnhqzQHEmhC8i4HWvhCs1x2LhjJtwSdHlnAkbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [物品 = _t, 利用者 = _t, 利用開始 = _t, 利用終了 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"物品", type text}, {"利用者", type text}, {"利用開始", type date}, {"利用終了", type date}}),
Custom1 = Table.Buffer(#"Changed Type")
in
Custom1
テストテーブル
let
ソース = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZpLkiUnDEX3UuOOMOgHGrrbu+jo/W/DSkmUnUhvWHUCUoA+V/B+//769fXjC8YYf4351xzPH3Pg/vrz4/fX3zcSkAWOft5oI22paD5os8aoXzcSHXP3oxawfBq1B1cCzx8gOut8aH8QbF11WQ9SQFr9KKXNox/FSKPZpwdNUKVqoY+ath0V0YN49kCYqEH8jNHR7N9D5sSp/aBl54h1lNgfiKvZiYcQnq19zbd8uTiarX0QzrV2PwrMwGbU9gNZiX7daIMZ3yMg3FiRPsZP3Y0ZD0JSprpk9bNi0R4pb2yOUd3dBRsLH7Smfa2fcLH9WXfjQVMYm9ByMzY38QNPQE61YcUMRwqTquc6Ip7VdicoS6sVgcaJ/dcoD9VFNR6dbMVV9x2eKN5DWat9jvaG0Y9CgtF860FrKHGd0EN1pFe/yBMlsFCbvRUfxLOxXcJnuNmLx+G3EDUnstw/BzYGrkh2e/SjLEi6LXS0xqyB7GgSreq6gYSgOeMnuHhbhq+jtmfWmehlvPoebmm214Nr7cYH1dPdJOnRZG1yBnq9+D7+Xzey+pMr/nkj2liSOBj1ZS39gCbynT8PWSy3gYlk7bJNiZhk3Pkukf2hd8k15LGwYN4V0hD6ZuhuRjkadlwVPXXGyiDeTnOQrOLWB80Bt4MaYk9PJwe9lvzECQBCsxteg4BK5jrIPOOOyUSWCrHZDQ+GmiSTgCWGxoodXiiN7dsjaO4PoxaPdWd4Q15ocGHjGu7W5ml3cCUic+u68S65JsHt1UnMMZrTykFDqlc7ogVyR/8ZNY8VFaGOOwlB6LRJqI2FHly05l2QITSczr3qxjuyjYe7MCRCM/EOfwjJJXMX/ZloT1jNhI6+RVdBNHB9mNBqdclCiR6RWQ/ZBd6khXfpSgQM0GzvgxR03bUwEZuF1eVd/NlBQg3yEH+WUWokh44bwtV5HbHgqgnFkfkaNxN6PdGhjfGu48wNPyHlVVNDSrzdDPLowlUKeSJUoHr+oYRMulTbQz8xNt8KpUG2rjoK3ECFmpMDAXCNhhA8DLsaH6OYmqrhqgZPr1MI8R53cT2D4DQTr1HegewmTJyw7Fu4QOgnNHeqnusiCXU2ScjRFvOZftSUWbRaImtAqOaMGEU0akaOb1lENifiWm1JN6ELMuvToEdTEZt993KiVNrZRKYZityFI60IGwu9ETIB1WzvjjOpQeL6ySJV7iPGEBqIIrdjJAKV0tsnYrx3CUOcTAS5F4WhQHjwvjNQIpmnCS4IttyBlUSR9721idDK8e1oGLJFUIvywyNblJv1Poisnb3ze6I1pXRBmNrEUlCzt94vMM6770uEexb3PBOqrNvRMBQIWolszPBECPPOaBhFXMW8uqIZ2XhX273mmtsWDZdIZs25Z9QTW/Vb7jRmel1x3OyM2ZnhTqOjZE+MOrgX3xkjiZoabwx09B3gBVlbVW5iEk1dzRmHFdal3zkN85YGdtHwidjalju1JtoCVM84yrtCyTOJCKzZqWZI7C7XYJiRdVeHvLxbX3WLAsxCjVI65ER7ajehJy6k0uycCXe92TsTCjXxH/W9iwUngqXWJdmm4GpSi+uWiUUGn1FrSLNgN8LOsZoeIsPS012cElkEjRomOWoU1YJHmozSICWyBo6rP7mSmNykwrhUGWcHC6JJWqM/RAsSVU+LCadoTUJxFTObHORkU6m4SewUS1N9pgOeNVYdCXKR4phKh6DcMCQysdikO1cSVnA7MyJVr3J7hyEXFModYpI5sAlwR9Y3l2uuM58dyIdPbWt0m83wb1nfXJMkxO0tl0YHQ2MArSLhMESGuXTp+hMxVGmSiHCNxmc0cnXj1Pg4tZ0j1ELoyDqgmnQxrkaRavDHIGvEq4F+VSTAzRnH3RNzs2LMC6FyfZfI+remLDhilTtUKQSXChZhSudWahY1lsjWO2+nSWSVvwg1Cg1HirMxwxHufe97IiYeH0ZZMzvuFVMotadHv3VQIlYqbwgUIs58ZjXf8nqM2MznddAU192wJoI1x33GiRaveftnInOnkvsphJp1wNjsrgey5erGjLiwUr29MJH1l8VpKJSf9ZBwh0mibUmt2cKI1lLD6VxYabmHS2T1ouSMRCQD7mRNqTItJddvxS2SzPLwcJCea46CyFLXnYS+kc5qfEjJNbAaH3dPikXDJ2KT/o0ZXj5llbbvINupeiZxi7S4Mz46JCqvHJRvgdaLNROGbgWqDhW6dZ97+JeFriW/X7J+3oh2fRxJZLm68Y1ANuEt4ii0JM9dVFciy653RaZQmdaWlsebRLhXE13xJjmKsKKQi8tSzS1pEsnTclUrohPf5YouEUwuDwWJ9mCqmSHMGLTuYk15YUVNKnQCIrP5lIekqeqY759A67/sD5py7IX8vso8d1Z0OqRmwrjXOz8ReKEQ45LPzi/kEhn2+IAmANRvxW8OBAEritb0JLyK5mzMcGTb1BgfPyywZP1hlBW89MIXiofHsRrj/bFFcDUTRp6EAQXFe+rs9tBfTc1nGuPjqXUAfUAbNO8DX8gtXAuab7nqBmnWBVGStSHuNTqpmc/LJJ3fbbxQ3oJkL/FCUfCOy7+QX5zh0OoaruPExFX9Vqg/Ok/I/yFOVWOF4Z6Qs8abHQ3yGm+J914yZ42nI5NeKH93UFz+IK5L5iyheNqTF/Lzp1kOmc8jAkqDPMcL0+2Giaz612UFEW3G+GnJGHVV+WuKOiba2b0a6yAkMtAHtEdj9yH4YZDorkuKwCIqaYajZZXGAzniwLqF22054mCvc/v9Qu7suu+Um4RQ5u21nG0fS7dJ8fMbKnHFeetswr9ZVeSfGvicTRDVvZXzUl2dTCJ4kPg+eUkBD+cZ6IWeFVv9xtt2idiZ9/bl/4W1LDfREt7NKK+cA/le7kHnvfH/xKON5yhOK+dFdJXMnsjiet/+ksj2vLimfP+oTW+3kPNKeYT4C3kDhNp9i91tz11lQYi1cEq+AU5a9aziF2BCpcCcUcDdHro6WrtZVwY+lJyaCERLuZWIVPz+sURB5uzcjPJ2EHQ0ZuRGlXQm+VD1/Qhc0BxStEIiGe2S4wJkfUJ7dOuKgBxyD1rnqeL8CPGFXLHi/oiA9ANCWiU7rYgTXcj3Pq1TsWp0rfw56XMf8PXnz78=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [物品 = _t, 利用日 = _t, 金額 = _t]),
変更された型 = Table.TransformColumnTypes(ソース,{{"物品", type text}, {"利用日", type date}, {"金額", Int64.Type}})
in
変更された型
回答例
let
Source = テストテーブル,
#"Merged Queries" = Table.NestedJoin(Source, {"物品"}, マスタテーブル, {"物品"}, "Source", JoinKind.LeftOuter),
Custom1 = Table.AddColumn(#"Merged Queries",
"newColumn",
each Table.SelectRows(_[Source],
(x)=>List.AllTrue({x[利用開始]<=_[利用日],
List.AnyTrue({x[利用終了]=null,x[利用終了]>=_[利用日]})
}
)
)
),
#"Expanded {0}" = Table.ExpandTableColumn(Custom1, "newColumn", {"利用者"}, {"利用者"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded {0}",{"Source"})
in
#"Removed Columns"