[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『PowerQuery(powerpivot? dax?)ででワイルドカード使った値と一致させて、データを置換する方法が知りたいです』（りり）
もし分かりましたら、教えていただけますと非常に助かります。
データ件数がネックで、excelでは難しく、accessでは時間かかり、
時にはデータファイル破損も何度も経験しており、
もし可能ならPowerPivotで一気に処理できるのでは、と
一縷の望みを持って質問させていただきました。
【やりたいこと】
テーブルXのB列を埋めたい。
テーブルYのA列とテーブルXのA列を一致させて、
テーブルYのB列をテーブルXのB列に一気に流し込みたい。
【前提条件】
テーブルXのデータ件数が 数千万件 あります。
Accessでデータを分けて、数百万件ずつ実行していましたが、
相当な時間かかる上煩雑なため、他の方法を模索したい。
【PowerQueryを使用した方法を知りたいです】
PowerQueryではデータ成形が可能で、バックに2億のデータをもった上での
処理も可能なので、ここで一度に処理できたら助かります。
方法がありましたら、DAX？ PowerPivot？ 組み方を知りたいです。
■テーブルX
A B 1 [置換前] [置換後★解] 2 トヨタ愛知 トヨタ 3 株式会社トヨタ トヨタ 4 トヨタ株式会社 トヨタ 5 トヨタ名古屋整備 トヨタ
■テーブルY(置換内容)
A B 1 [置換内容] [置換後] 2 *トヨタ トヨタ 3 *トヨタ株式会社 トヨタ 4 トヨタ* トヨタ
よろしくお願いいたします m(_ _)m
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
（マナ） 2022/08/13(土) 08:03
（マナ） 2022/08/13(土) 08:12
（りり） 2022/08/13(土) 09:24
ひとつの語を含む場合のケースを教えていただきましたが。
実際は、アスタリスクが複数存在する語もありまして。
その場合の式が知りたいです。
サンプルが悪くて申し訳ありません。
以下のようなケースを想定しています。
A B 1 [置換前] [置換後★解] 2 トヨタ愛知 トヨタ 3 株式会社トヨタ トヨタ 4 トヨタ株式会社 トヨタ 5 トヨタ名古屋整備 トヨタ 6 ミニトヨタ青森 トヨタ青森 7 田中トヨタ株式会社青森 トヨタ青森
■テーブルY(置換内容)
A B 1 [置換内容] [置換後] 2 *トヨタ トヨタ 3 *トヨタ株式会社 トヨタ 4 トヨタ* トヨタ 5 *トヨタ*青森* トヨタ青森
A列同士を結合し、YテーブルのB列の内容をXテーブルのB列に流し込みたいです。
PowerPivotで使用できる式が知りたいです。
M言語かと思いますが、知識がまだ浅く、単純結合の related では
うまくいませんでした。
お知恵をお借りできたら、大変助かります。
よろしくお願いいたします。
（ りり） 2022/09/23(金) 22:51
検索してみました https://www.google.com/search?q=powerQuary+RegularExpression （´・ω・`） 2022/09/24(土) 06:51
PowerQueryで正規表現というのは、かなりトリッキーな感じですね。 JavaScriptのRegExpを援用するようです。 こういう細工を求められるなら、いっそのこと隠居Zさん提唱の VBA対応が良いと思いました。
ちなみに、 トヨタ青森だけは残すというのは本当ですか？
テーブルY(置換内容)を上から実行していったら、 *トヨタ*青森* トヨタ青森 に行き着く前に、単なる「トヨタ」に変換されてしまいますよね。 そう単純には行かないでしょう。
(1)最初に、*トヨタ*青森* がある場合に、使用頻度の低い文字列（例:"$$$$$$"とか）に置換しておいて、 (2)*トヨタ*等を「トヨタ」に置換して、 (3)最後に"$$$$$$"を「トヨタ青森」に戻す といった工夫が必要でしょう。 テーブルYにも一層の工夫が必要で、VBAのほうが小回りが利いてたぶん簡単だと思います。 （まあ、私のPowerQueryレベルが低劣なだけかもしれませんが） （γ） 2022/09/24(土) 09:17
ちょっと浅知恵SQLで実験してみました。
WITH
Y AS (
SELECT '%トヨタ' AS A, 'トヨタ' AS B FROM DUAL
UNION ALL SELECT '%トヨタ株式会社' AS A, 'トヨタ' AS B FROM DUAL
UNION ALL SELECT 'トヨタ%' AS A, 'トヨタ' AS B FROM DUAL
UNION ALL SELECT '%トヨタ%青森%' AS A, 'トヨタ青森' AS B FROM DUAL
)
,X AS (
SELECT 'トヨタ愛知' AS A FROM DUAL
UNION ALL SELECT '株式会社トヨタ' AS A FROM DUAL
UNION ALL SELECT 'トヨタ株式会社' AS A FROM DUAL
UNION ALL SELECT 'トヨタ名古屋整備' AS A FROM DUAL
UNION ALL SELECT 'ミニトヨタ青森' AS A FROM DUAL
UNION ALL SELECT '田中トヨタ株式会社青森' AS A FROM DUAL
)
SELECT
X.A
,CASE WHEN X.A LIKE Y.A THEN Y.A END AS YA
,CASE WHEN X.A LIKE Y.A THEN Y.B END AS YB
FROM
X CROSS JOIN Y
↓実行結果
__|__________A___________|_______B_______|____C_____ 1|A |YA |YB 2|トヨタ愛知 | | 3|株式会社トヨタ |%トヨタ |トヨタ 4|トヨタ株式会社 | | 5|トヨタ名古屋整備 | | 6|ミニトヨタ青森 | | 7|田中トヨタ株式会社青森| | 8|トヨタ愛知 | | 9|株式会社トヨタ | | 10|トヨタ株式会社 |%トヨタ株式会社|トヨタ 11|トヨタ名古屋整備 | | 12|ミニトヨタ青森 | | 13|田中トヨタ株式会社青森| | 14|トヨタ愛知 |トヨタ% |トヨタ 15|株式会社トヨタ | | 16|トヨタ株式会社 |トヨタ% |トヨタ ← どちらに引っ掛かっても結果的には「トヨタ」だからいいかもだけど 17|トヨタ名古屋整備 |トヨタ% |トヨタ ← データベース的には2件該当、つまり一意になってないから、 18|ミニトヨタ青森 | | 仮にUPDATE文を書くとしても何らかの対策は必要。 19|田中トヨタ株式会社青森| | 20|トヨタ愛知 | | 21|株式会社トヨタ | | 22|トヨタ株式会社 | | 23|トヨタ名古屋整備 | | 24|ミニトヨタ青森 |%トヨタ%青森% |トヨタ青森 ← ↑と同様ではあるが、それ以前に期待する結果が得られていない 25|田中トヨタ株式会社青森|%トヨタ%青森% |トヨタ青森 ← (γさんからご指摘済みの件)
さて、どーしたものだか...
(面白そうなので、秘かに行く末見守っときます ^^; )
（白茶） 2022/09/24(土) 10:48
見守っとくにしても、ちょっとさすがに↑は乱暴過ぎましたかね。^^;
WITH
Y AS (
SELECT '^.*トヨタ$' AS A, 'トヨタ' AS B FROM DUAL
UNION ALL SELECT '^.*トヨタ株式会社$' AS A, 'トヨタ' AS B FROM DUAL
UNION ALL SELECT '^トヨタ.*$' AS A, 'トヨタ' AS B FROM DUAL
UNION ALL SELECT '^.*トヨタ.*青森.*$' AS A, 'トヨタ青森' AS B FROM DUAL
)
,X AS (
SELECT 1 AS SEQ, 'トヨタ愛知' AS A FROM DUAL
UNION ALL SELECT 2 AS SEQ, '株式会社トヨタ' AS A FROM DUAL
UNION ALL SELECT 3 AS SEQ, 'トヨタ株式会社' AS A FROM DUAL
UNION ALL SELECT 4 AS SEQ, 'トヨタ名古屋整備' AS A FROM DUAL
UNION ALL SELECT 5 AS SEQ, 'ミニトヨタ青森' AS A FROM DUAL
UNION ALL SELECT 6 AS SEQ, '田中トヨタ株式会社青森' AS A FROM DUAL
UNION ALL SELECT 7 AS SEQ, 'トヨタ部品株式会社青森' AS A FROM DUAL -- ←サンプル追加(課題提起)
)
SELECT DISTINCT
X.SEQ
,X.A
,Y.B
,CASE
WHEN REGEXP_LIKE(X.A , '^.*トヨタ.*青森.*$') THEN 'トヨタ青森' -- ←優先順位アゲてみる
WHEN REGEXP_LIKE(X.A , '^.*トヨタ$') THEN 'トヨタ'
WHEN REGEXP_LIKE(X.A , '^.*トヨタ株式会社$') THEN 'トヨタ'
WHEN REGEXP_LIKE(X.A , '^トヨタ.*$') THEN 'トヨタ'
END AS B2
FROM
X LEFT JOIN Y ON REGEXP_LIKE(X.A , Y.A)
ORDER BY
X.SEQ
↓実行結果
_|___A____|__________B___________|____C_____|____D_____ 1|SEQ |A |B |B2 2| 1|トヨタ愛知 |トヨタ |トヨタ 3| 2|株式会社トヨタ |トヨタ |トヨタ 4| 3|トヨタ株式会社 |トヨタ |トヨタ ← この場合ならDISTINCTで重複削除できる 5| 4|トヨタ名古屋整備 |トヨタ |トヨタ 6| 5|ミニトヨタ青森 |トヨタ青森|トヨタ青森 7| 6|田中トヨタ株式会社青森|トヨタ青森|トヨタ青森 8| 7|トヨタ部品株式会社青森|トヨタ |トヨタ青森 ←┐ 9| 7|トヨタ部品株式会社青森|トヨタ青森|トヨタ青森 ←┴ 表結合では2件該当。CASE式は優先順位次第(件数によっては大変だし)
しかし「数千万件」かぁ... 大変スね ^^;
（白茶） 2022/09/24(土) 17:58
紹介のありました記事を読んで、簡単なサンプルを作ることしかできませんでした。
テーブルのサンプルです。（Name2が置換後の列です） ID Name Name2 1 株式会社トヨタ トヨタ 2 ミニトヨタ株式会社 トヨタ 3 ミニトヨタ青森 トヨタ青森 4 トヨタ株式会社青森 トヨタ青森 5 株式会社青森 株式会社青森 どれもマッチしないデータ
詳細エディターにはこんなコードを入れています。
let
tbl = Table.FromRecords({
[ID = 1, Name = "株式会社トヨタ"],
[ID = 2, Name = "ミニトヨタ株式会社"],
[ID = 3, Name = "ミニトヨタ青森"],
[ID = 4, Name = "トヨタ株式会社青森"],
[ID = 5, Name = "株式会社青森"]
}),
// 文字列置換関数
replacer = (char as text) as text => Web.Page(
"<script>
var str1 = '" & char & "'.replace(/.*トヨタ.*青森.*/,'トヨタ青森');
if(str1 !='トヨタ青森')
str1 = '" & char & "'.replace(/.*トヨタ.*/,'トヨタ');
document.write(str1) ;
</script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},
// Name列の各要素にreplacerを作用させ、新しい列Name2を作成する
tbl2 = Table.AddColumn(tbl, "Name2", each replacer([Name]))
in
tbl2
各行ごとにStringオブジェクトのreplaceメソッドを呼んでいるので、効率は悪いでしょうね。 (というより、Web.Pageでテーブルを作る処理がネックになるかも） 数千万件ですか、ちょっと私の手には負いかねます。 頑張って下さい。
# いっそテキストファイルベースで、pythonなりrubyなりでテキスト処理したほうが手っ取り早いかも。 # 秀丸は1億行まではいけるそうですね。 （γ） 2022/09/25(日) 09:40
＞# 秀丸は1億行まではいけるそうですね。
5000万行300列 8GB CSVではじかれたことがあるのです。
秀丸で何か読み込むテクとかあれば教えていただけるとうれしいです。
（他の人の質問のところで、本当に申し訳ないのですが・・・）
（まっち） 2022/09/26(月) 17:08
ご指摘ありがとうございます。 https://caddiary.com/?p=2861 の記事タイトルをそのまま伝聞の形でお伝えしたまでです。 貴兄の経験が質問者さんのお役に立つことを願っております。 （γ） 2022/09/26(月) 17:51
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.