[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『条件一致引き算（パワークエリ）』（.:*.ゆ ゅ）
いつもお世話になっております。 下記のような表があったとします。
|[A] |[C]
[1] |経過時間[s]|ID
[2] | 0.014900|AAA
[3] | 0.015879|BBB
[4] | 0.034917|BBB
[5] | 0.035897|AAA
[6] | 0.055900|AAA
[7] | 0.056881|BBB
このうち、IDが同じもの同士のみ時間差分をとるというようなことをパワークエリ上でしたいです。 現在は数式を入れていて、 D2=[@[経過時間'[s']]]-XLOOKUP([@ID],$C1:C$2,$A1:A$2,0,0,-1) となりますが取り込むときに完結できたらいいと思いました。
クエリは取り込みにしか使ったことがないレベルです。 カスタム列を使う気はしますが範囲指定の方法などが理論的に良く分かっていません。 ご教授お願い致します。
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"経過時間[s]", type number}, {"ID", type text}}),
グループ化された行 = Table.Group(変更された型, {"ID"}, {{"group", each _, type table [#"経過時間[s]"=nullable number, 列2=any, ID=nullable text]}}),
連番0 = Table.TransformColumns(グループ化された行, {{"group", each Table.AddIndexColumn(_,"連番0"), type table}}),
連番1 = Table.TransformColumns(連番0, {{"group", each Table.AddIndexColumn(_,"連番1",1), type table}}),
前回値とマージ = Table.TransformColumns(連番1, {{"group", each Table.NestedJoin(_, {"連番0"}, _, {"連番1"}, "group2", JoinKind.LeftOuter)}}),
削除された他の列 = Table.SelectColumns(前回値とマージ,{"group"}),
#"展開された group" = Table.ExpandTableColumn(削除された他の列, "group", {"経過時間[s]", "ID", "group2"}, {"経過時間[s]", "ID", "group2"}),
#"展開された group2" = Table.ExpandTableColumn(#"展開された group", "group2", {"経過時間[s]"}, {"経過時間[s].1"}),
挿入された引き算 = Table.AddColumn(#"展開された group2", "減算", each [#"経過時間[s]"] - [#"経過時間[s].1"], type number),
削除された列 = Table.RemoveColumns(挿入された引き算,{"経過時間[s].1"})
in
削除された列
（マナ） 2022/09/26(月) 19:00
（マナ） 2022/09/26(月) 19:15
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"経過時間[s]", type number}, {"ID", type text}}),
並べ替えられた行 = Table.Sort(変更された型,{{"ID", Order.Ascending}, {"経過時間[s]", Order.Ascending}}),
追加されたインデックス = Table.AddIndexColumn(並べ替えられた行, "インデックス", 0, 1, Int64.Type),
追加されたインデックス1 = Table.AddIndexColumn(追加されたインデックス, "インデックス.1", 1, 1, Int64.Type),
マージされたクエリ数 = Table.NestedJoin(追加されたインデックス1, {"インデックス"}, 追加されたインデックス1, {"インデックス.1"}, "追加されたインデックス1", JoinKind.LeftOuter),
#"展開された 追加されたインデックス1" = Table.ExpandTableColumn(マージされたクエリ数, "追加されたインデックス1", {"経過時間[s]", "ID"}, {"経過時間[s].1", "ID.1"}),
挿入された引き算 = Table.AddColumn(#"展開された 追加されたインデックス1", "減算", each [#"経過時間[s]"] - [#"経過時間[s].1"], type number),
追加された条件列 = Table.AddColumn(挿入された引き算, "カスタム", each if [ID] = [ID.1] then [減算] else null),
削除された他の列 = Table.SelectColumns(追加された条件列,{"経過時間[s]", "ID", "カスタム"})
in
削除された他の列
（マナ） 2022/09/26(月) 19:25
マナさん、ありがとうございます。 手が空き次第確認させていただきます。取り急ぎお礼申し上げます。 （.:*.ゆ ゅ） 2022/09/27(火) 10:03
確認しました。 結果としては上手くいっているのですが、後学のために質問させてください。
インデックス2つ付与してその番号のズレによって行をズラしている事実は確認しましたが、 なぜマージした後に展開することで1行ズレた列が生成されるのか理解が追い付いていません。 （そもそもマージ自体の挙動がTANAKAさんの http://officetanaka.net/excel/function/GetAndTransform/13.htm の記事に記載されていることぐらいしか分からないというのが大きい気はします）
直接のご説明でなくとも、学べるサイトがあればうれしいです。 よろしくお願いいたします。 （.:*.ゆ ゅ） 2022/09/27(火) 10:48
>展開することで1行ズレた列が生成 展開で生成されるわけではないです
テーブルのマージは、2つの表を、同じキーを持つ行同士で結合させます 結合の結果は、追加された列にテーブルとして格納されます。 なぜテーブルかというと、複数行がマッチする場合があるからそういう仕様になっているのだと思います
今回は、1対1で対応するので、1テーブルに1行しか格納されてませんが、 この段階で既に1行ずれた行が結合されてます。 （同じインデックスをもつ行どうしが結合されますが、インデックスを1番づつずらしている） （´・ω・`） 2022/09/27(火) 13:12
（´・ω・`）さん、ありがとうございます。 ＞テーブルのマージは、2つの表を、同じキーを持つ行同士で結合させます この概念自体はわかるのですが TANAKAさんのサイトのように、横に別のアイテムがある場合しか動きがイメージできないというか…
今回の例で行くとインデックス番号同士でマージしていると思うのですが、 テーブルの中がどういう状態かわからないのです。（そのため展開が理解できていない） 同じ"1"というキーで見たときに、1行目と2行目にいるというのをテーブル内で保存されてる感じですか？ その状態で、1行目に1がいるほう(追加されたインデックス1)のデータを基準に展開すると、 マージされたことにより2行目から開始されるので見た目上ズレる？
ぼんやり理解はできるのですが一切言語化できません。 元の状態よりは理解できました。ありがとうございます。 （.:*.ゆ ゅ） 2022/09/27(火) 13:52
こういう感じなんですけど マナさんのクエリでは、2つの表をつくってなくて、1つの表に2列インデックスをつくってますが 考え方は同じです。
表1 表2 [1] |経過時間[s]|ID | インデックス1 [1] |経過時間[s]|ID | インデックス2 [2] | 0.014900|AAA | 0 [2] | 0.014900|AAA | 1 [3] | 0.035897|AAA | 1 [3] | 0.035897|AAA | 2 [4] | 0.055900|AAA | 2 [4] | 0.055900|AAA | 3
表1と表2をマージした表（表1インデックス1=表2インデックス2でマージ） [1] |経過時間[s]|ID | インデックス1|経過時間[s]|ID | インデックス2 [2] | 0.014900|AAA | 0 | null lnul l null [3] | 0.035897|AAA | 1 | 0.014900|AAA | 1 [4] | 0.055900|AAA | 2 | 0.035897|AAA | 2
たぶん.:*.ゆ ゅさんはわかっているけど、腑に落ちないってことでしょうか？ （´・ω・`） 2022/09/27(火) 14:14
表に示してくださりありがとうございます。 マージまでは認識があっていたようです。 2022/09/27(火) 13:12の（´・ω・`）さんの説明からそこまでは理解できました。
やはり、展開が怪しいのだと思います。 マージの段階だと表1と2が混在？というか並列に存在している印象がありまして。 それなのに展開するときに、表2のほうだけ展開している現象が"腑に落ちない"のだと思います。 （.:*.ゆ ゅ） 2022/09/27(火) 14:29
>並列に存在している印象 並列・・・ではないように感じています
こんな2つの表だと
表1 表2
[1] |KEY |名前 [1] | KEY | 物品
[2] | 1 | あ [2] | 1 | カメラ
[3] | 2 | い [3] | 1 | デスクトップPC
[4] | 3 | う [4] | 2 | ビデオ
[5] | 2 | ノートPC
[6] | 3 | スマホ
[7] | 3 | タブレット
こうなります。
表1と表2をマージ（表1KEY=表2KEY） [1] |KEY |名前 | 表2 [2] | 1 | あ | Table ← 列の中にテーブルが格納されている [3] | 2 | い | Table [4] | 3 | う | Table
表2を展開すると [1] |KEY |名前 | KEY | 物品 [2] | 1 | あ | 1 | カメラ [3] | 1 | あ | 1 | デスクトップPC [4] | 2 | い | 2 | ビデオ [5] | 2 | い | 2 | ノートPC [6] | 3 | う | 3 | スマホ [7] | 3 | う | 3 | タブレット
表1を主として、表2がくっつく（従）感じに思います このあたりの感じは、結合方法にも依るかもしれません （´・ω・`） 2022/09/27(火) 15:08
すごく参考になりました。1人で勝手に盛り上がってます ^^; (当方ようやく手元の2010にアドイン版を入れてみたものの、まだ全然使えてない有様...)
あんまり需要無いだろうけど マナさんのクエリをSQL(ORACLE向け)で再現してみたら↓こんな感じの考え方になりますでしょうか。
WITH TABLE1 AS (
SELECT 0.0149 AS "経過時間", 'AAA' AS "ID" FROM DUAL
UNION ALL SELECT 0.035897 AS "経過時間", 'AAA' AS "ID" FROM DUAL
UNION ALL SELECT 0.0559 AS "経過時間", 'AAA' AS "ID" FROM DUAL
UNION ALL SELECT 0.015879 AS "経過時間", 'BBB' AS "ID" FROM DUAL
UNION ALL SELECT 0.034917 AS "経過時間", 'BBB' AS "ID" FROM DUAL
UNION ALL SELECT 0.056881 AS "経過時間", 'BBB' AS "ID" FROM DUAL
)
, TABLE2 AS (
SELECT
経過時間
,ID
,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID,経過時間) -1 AS SEQ0
,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID,経過時間) AS SEQ1
FROM
TABLE1
)
SELECT
A.経過時間
,A.ID
,A.経過時間 - B.経過時間 AS "引き算"
FROM
TABLE2 A
LEFT JOIN TABLE2 B ON A.ID = B.ID AND A.SEQ0 = B.SEQ1
ORDER BY
A.ID
,A.SEQ1
「LEFT JOIN」は「LEFT OUTER JOIN」の省略なので 表結合についてはやはりデータベース言語同士、概念も表現も似てるっぽい感じですね。 おもしろいッスー。
(あ。スミマセン。お邪魔しました... ^^; )
（白茶） 2022/09/27(火) 15:38
＞表1を主として、表2がくっつく（従）感じに思います この説明で凡そ納得いたしました。0始まりのほうを表1として考えれば確かに今回の挙動になります。 今まで他のスレッドでもマージ、便利なんだろうなと思いつつ見て見ぬふりをしてきたので この機会に整理できてよかったです。（´・ω・`）さんもマナさんもありがとうございます。 勉強中なので、ひっそり[[20220925130324]]を楽しみたいです。
私はSQL触ったことない人間ですが、似ているらしいですね。 恐らく元々データベース系の教養がある方ならもっと楽に理解できるのかななど思っております。 （.:*.ゆ ゅ） 2022/09/27(火) 16:33
＞今回の例で行くとインデックス番号同士でマージしていると思うのですが、 ＞テーブルの中がどういう状態かわからないのです。（そのため展開が理解できていない）
１）適用したステップで、「マージされたクエリ数」を選択 ２）中央のプレビュー画面で、値が Table と表示されているところを選択 ３）プレビュー画面の下部に、テーブルの内容が確認できます。 ４）次のステップでは、この中で必要な列 経過時間[s] のみを展開しています。
（マナ） 2022/09/27(火) 16:51
ありがとうございます。 テーブルの内容、そこから確認できたのですね。 （一括プレビューしか頭になく、セル単体で押していませんでした） 実際値見たらより理解しやすかったです。なるほどなあと。 （.:*.ゆ ゅ） 2022/09/27(火) 16:59
行のグループ化 = Table.Group(
テーブル1, {"ID"}, {"経過時間", each [
経過1=_[#"経過時間[s]"] & {""},
経過2= {0} & _[#"経過時間[s]"],
結合 = Table.FromColumns({経過1, 経過2}, type table [経過1=number, 経過2=number]),
行末削除 = Table.FirstN(結合, each [経過1] <> ""),
引き算 = Table.AddColumn(行末削除, "経過時間", each [経過1]-[経過2])
][引き算], type table[経過時間=number]
}),
列の展開 = Table.ExpandTableColumn(行のグループ化, "経過時間", {"経過時間"})
（d-q-t-p） 2022/09/28(水) 14:34
ありがとうございます。同じ結果が出ることは確認しましたが、一切理解できていません。 今日は時間が取れなそうなので明日悩みます。 （.:*.ゆ ゅ） 2022/09/28(水) 15:12
確認してみました。 経過1と経過2って、それぞれ""と0と結合しているように見えるのですが これはどういう意図で行われているのかをお教えください。 経過1は行末削除のところで空白との比較が入っているのでそのためかなとは思ったのですが そうなると経過2はなぜ…など分からず。 （.:*.ゆ ゅ） 2022/09/29(木) 10:54
私もまだ d-q-t-p さんのクエリを理解できてませんが、 >経過1と経過2って、それぞれ""と0と結合している
以下のクエリで確認できますが、
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"経過時間[s]", type number}, {"ID", type text}}),
カスタム1 = 変更された型[ID], // テーブルの行を取り出すとリスト
カスタム2 = {0} & カスタム1 // リストの先頭に要素を追加
in
カスタム2
元の経過時間の列がリストとして渡されるので、 その先頭に 0 を追加したリスト ← 経過1 その最後に "" を追加したリスト ← 経過2 になるようです （´・ω・`） 2022/09/29(木) 11:21
すみません逆です （´・ω・`） 2022/09/29(木) 11:22
仮にデータの中に「null」が混じっていたら Table.FirstNで
「[経過1] <> null」とした時に「null」がある行までのデータしか抜き出
してくれません。
https://qiita.com/PowerBIxyz/items/cec5f20a5cd493ea8bc7
こちらの記事の最後のやり方を参考にしています。
記事では被らないよう「Number.NegativeInfinity」を使っていますね。
「行のグループ化」の最後の「[引き算]」のところを他のステップ名に
変えると どこで何が起きているのか確認しやすいかと思います。
（d-q-t-p） 2022/09/29(木) 12:23
私、だんだんわかってきました ということで、こういうことでもいいのかなと d-q-t-pさんのクエリは、この関数のところをレコードを使ってうまくやってますが、 自分の理解のために、リストの関数で書き直してみました
関数クエリ（リストの差分）
(argList as list)=>
let
ZipList = List.Zip( {List.RemoveFirstN(argList,1) , List.RemoveLastN(argList,1)} ),
Diff = List.Transform( ZipList , each _{0}-_{1} )
in
Diff
リストの差分をつかったグループ化
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
経過時間のリスト = Table.Group(ソース, {"ID"}, {{"経過時価の差分", each リストの差分([#"経過時間[s]"]), type list}}),
#"展開された 経過時価の差分" = Table.ExpandListColumn(経過時間のリスト, "経過時価の差分")
in
#"展開された 経過時価の差分"
（´・ω・`） 2022/09/29(木) 14:06
お二方の説明を見て、やっていることは理解できました。 高度すぎるので自分からこれを思いついて記述するのは現状無理ですが。 改造するのでギリギリという感じです。 ただ、いろんなアプローチがあることを知っておくだけでも知見が広がるので感謝です。 （.:*.ゆ ゅ） 2022/09/29(木) 15:28
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.