[[20220707113412]]　『パワークエリ　行数のカウント　をグループ化を使』（まっち）　ページの最後に飛ぶ

『パワークエリ　行数のカウント　をグループ化を使わずに再現したい』（まっち）

エクセルでいうCOUNTIFの機能の再現です。
　　　　A　　　　B
1　　　あ
2　　　あ
3　　　あ
4　　　い
5　　　い
6　　　う

B列にA列の総数をすべての行に入れたい

　　　　A　　　　B
1　　　あ　　　　3
2　　　あ　　　　3
3　　　あ　　　　3
4　　　い　　　　2
5　　　い　　　　2
6　　　う　　　　1

現在、グループ化の行数のカウントで別のテーブルを作り、
マージで対応しています。

行より上の累計であれば、
List.Sum(List.FirstN(追加されたカスタム{[#"??"=[#"??"]]}[カウント][値],[カスタム.連番])))
みないにできるのですが、列全部を範囲にする方法が思い浮かばないのです。

何か方法があればご教示ください。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 >グループ化を使わずに
 なにが気にいらないんでしょう？
（´・ω・`） 2022/07/07(木) 12:01

まったくです。

利用部門曰く
「処理が遅い」（たしかに１００万行近い処理なので・・・。グループ化が原因ではないと思うのですが）
「複数のテーブルができるのは規程上だめ」（機微な個人情報だからだそうです・・・）
（まっち） 2022/07/07(木) 12:49

 多分、グループ化より遅いと思いますが

 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"列1", type text}}),
    列1 = List.Buffer(変更された型[列1]),
    追加されたカスタム = Table.AddColumn(変更された型, "カスタム", (r)=>List.Count(List.Select(列1 ,each _ = r[列1])))
 in
    追加されたカスタム

 >複数のテーブルができる
 シート上に読み込まなきゃユーザー側からはわかりませんよ

 質問するときに列のタイトルくらいつけてもらえませんかね？
 パワークエリ使ってるなら、列タイトル必要でしょ
（´・ω・`） 2022/07/07(木) 12:56

ありがとうございます。

すみません。
以後気を付けます。

（余談）
接続だから大丈夫ですと抗弁したものの聞いてもらえず。
（もうやめたい）
（まっち） 2022/07/07(木) 13:01

＞複数のテーブルができるのは規程上だめ
行をグループ化してマージするだけだから「複数のテーブル」を作る必要はないです。

見出しすらよく分かりませんが「テーブル1」に「列1」があるとして

    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    行のグループ化 = Table.Group(ソース, {"列1"}, {"個数", each Table.RowCount(_)}),
    クエリのマージ = Table.NestedJoin(テーブル1, {"列1"}, 行のグループ化, {"列1"}, "マージ", JoinKind.LeftOuter),
    マージ列の展開 = Table.ExpandTableColumn(クエリのマージ, "マージ", {"個数"}, {"個数"})

＞グループ化が原因ではないと思うのですが
適切な場所にバッファーを入れていないのでは？
（d-q-t-p） 2022/07/07(木) 13:02

失礼「Table.NestedJoin(テーブル1,……」→「Table.NestedJoin(ソース,……」です。
（d-q-t-p） 2022/07/07(木) 13:05

おぉなるほど。
ありがとうございます。

テーブルを複製して、グループ化して
それをマージしてました。

規程、突破しました！
ありがとうございます！！！
（まっち） 2022/07/07(木) 13:18

解決いたしました。
似たようなこと（パワークエリでエクセル再現）で悩んでいる方のために

?? 品目値
1 あ 10
1 あ 20
1 あ 30
1 あ 40
1 あ 50
2 い 100
2 い 110
2 い 120
3 う 200
3 う 210
3 う 220
3 う 230

というシートでCOUNTIFやSUMIFを再現させる
※上の行までの集計と列全範囲の集計です。

let

    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型2 = Table.TransformColumnTypes(ソース,{{"値", Int64.Type}}),
    変更された型 = Table.TransformColumnTypes(変更された型2,{{"??", Int64.Type}, {"品目", type text}, {"値", Int64.Type}}),
    グループ化された行 = Table.Group(変更された型, {"??"}, {{"カウント", each _, type table}}),
    追加されたカスタム = Table.AddColumn(グループ化された行, "カスタム", each Table.AddIndexColumn([カウント],"連番",1,1)),
    #"展開された カスタム" = Table.ExpandTableColumn(追加されたカスタム, "カスタム", {"??", "品目", "値", "連番"}, {"カスタム.??", "カスタム.品目", "カスタム.値", "カスタム.連番"}),
    変更された型1 = Table.TransformColumnTypes(#"展開された カスタム",{{"カスタム.値", Int64.Type}, {"カスタム.連番", Int64.Type}, {"カスタム.??", Int64.Type}, {"カスタム.品目", type text}}),
    追加されたカスタム1 = Table.AddColumn(変更された型1, "カスタム", each List.Sum(List.FirstN(追加されたカスタム{[#"??"=[#"??"]]}[カウント][値],[カスタム.連番]))),
    リストバッファ = List.Buffer(追加されたカスタム1[カスタム.品目]),
    追加されたカスタム2 = Table.AddColumn(追加されたカスタム1, "全範囲のCOUNTIF", (r)=>List.Count(List.Select(リストバッファ ,each _ = r[カスタム.品目]))),
    行のグループ化 = Table.Group(追加されたカスタム2, {"カスタム.品目"}, {{"カウント", each List.Sum([カスタム.値]), type number}}),
    テーブルバッファ = Table.Buffer(行のグループ化),
    クエリのマージ = Table.NestedJoin(追加されたカスタム2, {"カスタム.品目"}, テーブルバッファ, {"カスタム.品目"}, "マージ", JoinKind.LeftOuter),
    #"展開された マージ" = Table.ExpandTableColumn(クエリのマージ, "マージ", {"カウント"}, {"全範囲のSUMIF"})
in
    #"展開された マージ"

（まっち） 2022/07/07(木) 14:46

＞似たようなこと（パワークエリでエクセル再現）で悩んでいる方のために
誰も悩んでいませんよ。
悩んでいるのは貴方だけですよ。
（まっちぼう） 2022/07/07(木) 14:51

 ちょっとデータ数を１００万行に増やして試行してみましたが、

     追加されたカスタム = Table.AddColumn(変更された型, "カスタム", (r)=>List.Count(List.Select(列1 ,each _ = r[列1])))

 の方法は、グループ化する方法に比べて、超絶遅いです
 非推奨というより、使わないことを推奨
（´・ω・`） 2022/07/07(木) 15:07

ありがとうございます。
超絶遅いので、利用部門よりブーイングの嵐。
それでも上層部は、「規程優先！」

（まっち） 2022/07/07(木) 15:35

 なにがどうなっているのか理解できませんが、
 d-q-t-pさんの方法で解決したのではないのですか？
 2022/07/07(木) 14:46 の書き込みでもグループ化つかってますよね？

 これだけでいいのでは？

 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"??", Int64.Type}, {"品目", type text}, {"値", Int64.Type}}),
    グループ化された行 = Table.Group(変更された型, {"??"}, {{"カウント", each Table.RowCount(_), type number}, {"合計", each List.Sum([値]), type number}}),
    マージされたクエリ = Table.NestedJoin(変更された型,{"??"},グループ化された行,{"??"},"カスタム",JoinKind.LeftOuter),
    展開されたカスタム = Table.ExpandTableColumn(マージされたクエリ, "カスタム", {"カウント", "合計"}, {"カスタム.カウント", "カスタム.合計"})
 in
    展開されたカスタム
（´・ω・`） 2022/07/07(木) 16:00

すみません。

 d-q-t-pさんの方法で解決しているのに余計なものをアップしてしまいました。

理想は、 d-q-t-pさんの方法ですが、
現実は、 List.Count(List.Select(列1 ,each _ = r[列1]))の激遅で対応してます（許可が出なかったので）

コードが書かれているサイトは、勉強になるので、余計なお世話でアップしてしまいました。
高度な悩み以外は余計なことはしないように注意いたします。

（まっち） 2022/07/07(木) 16:40

＞許可が出なかった

理由がわかりませんが、これだとどうですか。
　

 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"??", Int64.Type}, {"品目", type text}, {"値", Int64.Type}}),
    グループ化された行 = Table.Group(変更された型, {"??"}, {{"テーブル", each _, type table [#"??"=nullable number, 品目=nullable text, 値=nullable number]}, {"カウント", each Table.RowCount(_), Int64.Type}, {"合計", each List.Sum([値]), type nullable number}}),
    削除された列 = Table.RemoveColumns(グループ化された行,{"??"}),
    #"展開された テーブル" = Table.ExpandTableColumn(削除された列, "テーブル", {"??", "品目", "値"}, {"??", "品目", "値"})
 in
    #"展開された テーブル"

（マナ） 2022/07/07(木) 17:33

御礼
ありがとうございます。

みなさまの効率的ないろいろなコードを拝見すると
とっても勉強になります。

利用部門から無意味な要求をクリアするために
無意味なコードを作っているときに、こういう
きれいなコードを見ると癒されます・・・。

（まっち） 2022/07/08(金) 08:31

マナさま

ありがとうございます！
これならOKと許可出ました。
（なぜ？かは聞かないでください。私も理解できません・・・）

本当に助かりました！
（まっち） 2022/07/08(金) 08:43

理由は、グループ化したのを接続専用としても
別のものができるのがダメなんだそうです（なぜ？）

（´・ω・`）さんのコードで最終OKとなりました。

ありがとうございました。
（まっち） 2022/07/08(金) 08:53

コメント返信：

[ 一覧(最新更新順) ]

[[20220707113412]] 『パワークエリ 行数のカウント をグループ化を使』（まっち） ページの最後に飛ぶ

[[20220707113412]]　『パワークエリ　行数のカウント　をグループ化を使』（まっち）　ページの最後に飛ぶ