Techの手帖

IT技術に関する小ネタを見つけたときに書くブログ

ExcelとPower Query(M言語)で並べ替えの挙動が違う件

両者は全くの別物なので注意。

Power QueryをExcelの延長という感覚で使っていると、思わぬ振る舞いをすることがあります。

ExcelとPower Queryの並べ替えの違い

大文字・小文字

Excel の場合

Excelでは、基本的に大文字と小文字は等価と見なされます。 *1

なので、並べ替えをすると「A」と「a」は隣接します。

図1. Excelの並べ替え(昇順)

「A」が先に来るか「a」が先に来るかは元のデータ次第です。

ちなみに、並べ替えのオプションに「大文字と小文字を区別する」という項目があり、これをチェックすると小文字→大文字の順に強制できます。

Power Query の場合

一方、Power Queryでは大文字と小文字は別の文字という扱いです。

昇順の場合は、常に大文字→小文字の順になります。

図2. Power Queryの並べ替え(昇順)

図を見てもらうと分かるように、Excelとはかなり違った並びになっています。

ひらがな・カタカナ

Excel の場合

同じような現象はひらがな・カタカナでも起こります。

どういうルールなのか分かりませんが、Excelには「読み」を考慮した独自の並べ替えの基準があるようです。

図3. Excelの並べ替え(昇順)

Power Query の場合

昇順の場合は、ひらがな→カタカナの順になります。

図4. Power Queryの並べ替え(昇順)

Power QueryでExcelライクな並べ替えをしたいとき

残念ながら完全な再現は多分できません。素直にExcelで並べ替えるのが得策です。

ただし英数字に関してだけなら、先に大文字に統一しておくことで大文字・小文字を区別しない(case insensitiveな)並べ替えは可能です。

図5. Power Queryで大文字・小文字を区別しない並べ替え

数式のeach Text.Upper("列1")がポイント。

思い込みの罠に気をつける

「並べ替え」や「検索」といった基本的な機能でも、アプリによって結構違いがあります。

環境を切り替えて作業をするときは、本当に意図した通りの変更になっているか疑ってかかったほうが良いと思います。

使い慣れている人ほどハマりやすいところだと思うので気をつけましょう。

*1:数式 ="a"="A" は TRUE を返します。