[[20220819143852]]　『パワークエリ：和暦文字列日付を西暦変換』（まっち）　ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

　

『パワークエリ：和暦文字列日付を西暦変換』（まっち）

レガシーなシステムをパワークエリへ変換しています。
以下のテーブルがあるとします。（１列のみです）
和暦文字
1450729
2010730
2151224
3011225
3640107
4010108
4310430
5010501

これは、7文字固定の文字列データです。
左の１文字目は、5＝令和、4＝平成、3＝昭和、2＝大正、1＝明治という規則です。

これを、以下のようにしたいのです。

（やりたいこと）
和暦文字　 西暦年（数字ではなく文字列）
1450729　 1912
2010730　 1912
2151224　 1926
3011225　 1926
3640107　 1989
4010108　 1989
4310430　 2019
5010501　 2019

（やったこと）
let 

    ソース = Excel.CurrentWorkbook(){[Name="テーブル5"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"和暦文字", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "西暦年（数字ではなく文字列）", each if Text.From(Text.Start([和暦文字], 1))=5 then Number.From(Text.Middle([和暦文字], 1, 2))+2018 else if Number.From(Text.Start([和暦文字], 1))=4 then Number.From(Text.Middle([和暦文字], 1, 2))+1988 else if Number.From(Text.Start([和暦文字], 1))=3 then Number.From(Text.Middle([和暦文字], 1, 2))+1925 else if Number.From(Text.Start([和暦文字], 1))=2 then Number.From(Text.Middle([和暦文字], 1, 2))+1911 else if Number.From(Text.Start([和暦文字], 1))=1 then Number.From(Text.Middle([和暦文字], 1, 2))+1867 else 0)
in
    追加されたカスタム

（結果）
和暦文字　 西暦年（数字ではなく文字列）
1450729　 1912
2010730　 1912
2151224　 1926
3011225　 1926
3640107　 1989
4010108　 1989
4310430　 2019
5010501　 0

なぜか最後だけゼロになるのです。
原因を捜索中です。

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

 each if Text.From(Text.Start([和暦文字], 1))=5 then
 ↓
 each if Number.From(Text.Start([和暦文字], 1))=5 then

（西瓜） 2022/08/19(金) 15:22

西瓜さま
ご連絡ありがとうございます。
ご指摘のとおり、 Number.Fromで実行すると、2019になるのです。
でも、利用部門のオーダーが「文字列で出力せよ」とのことで四苦八苦なのです。
（個人的には「なんで！」といいたいのですが、利用部門は利用部門で、コピーして値を貼り付けといったことすらできないアルバイトに作業させているといった台所事情があるようで・・・みんな意味のない工程をエクセルにやらせていたりします・・・当然「Numberで出した後書式設定すればいいじゃん！」と抵抗するもののそれすらできないらしい・・・）
（まっち） 2022/08/19(金) 15:30
 西瓜さんはそういうことをいっているのではなく、
 if の条件として、5という数字と比較するならNumber
 Textなら"5" と比較するんですよと。


 一行で書けばいいというものでもないですが。


 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"和暦文字", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "西暦年（数字ではなく文字列）", each Text.From(Date.Year(Date.From({"","M","T","S","H","R"}{Number.From(Text.Start(_[和暦文字],1))} & Text.Middle(_[和暦文字],1,2) & "/" & Text.Middle(_[和暦文字],3,2) & "/" & Text.Middle(_[和暦文字],5,2)))),type text)
 in
    追加されたカスタム
（´・ω・`） 2022/08/19(金) 15:40
（´・ω・`） さま

＞ if の条件として、5という数字と比較するならNumber
＞ Textなら"5" と比較するんですよと。

言われて、こんな初歩で赤面状態です。
いつもありがとうございます。
毎度のことながら、データ型で躓いて申し訳ございません。

本件解決しました。
変な質問データのこしてしまいすみません。
（まっち） 2022/08/19(金) 15:50

 こういうステップを追いかけた方が分かり易いかもしれません
 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"和暦文字", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "era", each {"","M","T","S","H","R"}{Number.From(Text.Start(_[和暦文字],1))},type text),
    追加されたカスタム1 = Table.AddColumn(追加されたカスタム, "カスタム", each Text.End([和暦文字],6)),
    位置によって分割された列 = Table.SplitColumn(追加されたカスタム1, "カスタム", Splitter.SplitTextByRepeatedLengths(2), {"カスタム.1", "カスタム.2", "カスタム.3"}),
    変更された型1 = Table.TransformColumnTypes(位置によって分割された列,{{"カスタム.1", type text}, {"カスタム.2", type text}, {"カスタム.3", type text}}),
    結合された列 = Table.CombineColumns(Table.TransformColumnTypes(変更された型1, {{"カスタム.1", type text}, {"カスタム.2", type text}, {"カスタム.3", type text}}, "ja-JP"),{"カスタム.1", "カスタム.2", "カスタム.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"結合済み"),
    結合された列1 = Table.CombineColumns(結合された列,{"era", "結合済み"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"結合済み.1"),
    追加されたカスタム2 = Table.AddColumn(結合された列1, "カスタム", each Date.From([結合済み.1])),
    追加されたカスタム3 = Table.AddColumn(追加されたカスタム2, "カスタム.1", each Text.From(Date.Year([カスタム])))
 in
    追加されたカスタム3
（´・ω・`） 2022/08/19(金) 15:52
（´・ω・`） さま

　ありがとうございます。
　知らない関数もあり、すぐには理解できないレベルですが、勉強させていただきます。

　以下が私の今のレベルです。（和暦文字列7文字を西暦表示するためにつくったやつです。）
　あまりにも汚くて恥ずかしいですが・・・。 

    追加されたカスタム = Table.AddColumn(和暦文字, "西暦年", each if Number.From(Text.Start([和暦文字], 1))=5 then Date.From(#date(Number.From(Text.Middle([和暦文字], 1, 2))+2018,Number.From(Text.Middle([和暦文字], 3, 2)),Number.From(Text.End([和暦文字], 2)))) else if  Number.From(Text.Start([和暦文字], 1))=4 then Date.From(#date(Number.From(Text.Middle([和暦文字], 1, 2))+1988,Number.From(Text.Middle([和暦文字], 3, 2)),Number.From(Text.End([和暦文字], 2)))) else if  Number.From(Text.Start([和暦文字], 1))=3 then Date.From(#date(Number.From(Text.Middle([和暦文字], 1, 2))+1925,Number.From(Text.Middle([和暦文字], 3, 2)),Number.From(Text.End([和暦文字], 2)))) else if Number.From(Text.Start([和暦文字], 1))=2 then Date.From(#date(Number.From(Text.Middle([和暦文字], 1, 2))+1911,Number.From(Text.Middle([和暦文字], 3, 2)),Number.From(Text.End([和暦文字], 2)))) else if Number.From(Text.Start([和暦文字], 1))=1 then Date.From(#date(Number.From(Text.Middle([和暦文字], 1, 2))+1867,Number.From(Text.Middle([和暦文字], 3, 2)),Number.From(Text.End([和暦文字], 2)))) else 0),
（まっち） 2022/08/19(金) 16:04

 できたのなら、それでよかったです。
 参考として、
 List = {0,1867,1911,1925,1988,2018}
 というリストがあったとき、リストの最初の要素は、List{0} です。
 List{Number.From(Text.Start(_[和暦文字],1))} を使うとif文のネストがいらなくなりますね。たぶん
（´・ω・`） 2022/08/19(金) 16:56
（´・ω・`） さま

今回、ｉｆ文をリストでやる方法が大きな収穫です。
ありがとうございました。

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

一応、コード編集なしで
操作だけでもできるということで。

自動記録されたコードがこれ。 

 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"和暦文字", type text}}),
    追加された条件列 = Table.AddColumn(変更された型, "カスタム", each if Text.StartsWith([和暦文字], "1") then "明治" else if Text.StartsWith([和暦文字], "2") then "大正" else if Text.StartsWith([和暦文字], "3") then "昭和" else if Text.StartsWith([和暦文字], "4") then "平成" else if Text.StartsWith([和暦文字], "5") then "令和" else null),
    抽出されるテキスト範囲 = Table.TransformColumns(追加された条件列, {{"和暦文字", each Text.Middle(_, 1, 2), type text}}),
    追加されたサフィックス = Table.TransformColumns(抽出されるテキスト範囲, {{"和暦文字", each _ & "年", type text}}),
    結合された列 = Table.CombineColumns(追加されたサフィックス,{"カスタム", "和暦文字"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"結合済み"),
    挿入された年 = Table.AddColumn(結合された列, "年", each Date.Year(Date.From([結合済み])), Int64.Type),
    変更された型1 = Table.TransformColumnTypes(挿入された年,{{"年", type text}})
 in
    変更された型1

（マナ） 2022/08/20(土) 00:07

横から　失礼、
A列に　元データが、
入っていると　します。
_

基本式

=YEAR(CHOOSE(LEFT(A2,1),"明治","大正","昭和","平成","令和")&MID(A2,2,2)&"年"&MID(A2,4,2)&"月"&MID(A2,6,2)&"日")
_

実現法、

例えば　5010501なら、
デコードして　令和01年05月01日
と.
日付文字列を　生成します:。

_

エクセルは、

此を　既に、
日付と　認識してますので、
year構文で　西暦を、
抽出できます。
_

でも、

関係ないのですよね、
失礼しました。
_

検証ファイル、

https://1drv.ms/x/s!AjviygfJDgV_gY9ObN9WlmNqw1dFYw
_

尚、

ファイルは　1度、
ローカルに　別名保存して、
其の　保存ファイルを、
扱うように　してくださいね、

別名保存でないと、
意味が　無いですよ。
_

さすれば、

閲覧も、編集も、
叶うものと　思いますよ。
（Nouble） 2022/08/20(土) 08:56

（´・ω・`）さんのを少し短くしてみました。 

 追加されたカスタム = Table.AddColumn(変更された型, "西暦年（数字ではなく文字列）",
    each Text.From(Date.Year(Date.From(
        {"","M","T","S","H","R"}{Number.From(Text.Start([和暦文字],1))} & Text.Middle([和暦文字],1,2)&"年"
    ))), 
    type text
 )

（マナ） 2022/08/21(日) 12:32

↑これ以上長くなるようだとカスタム関数にしたほうがわかりやすいです。

（マナ） 2022/08/21(日) 13:07

＞叶うものと　思いますよ。
宗教団体の一員かも。
（わらわせるね） 2022/08/21(日) 14:06
余計なコメントが入ったので…
カスタム関数バージョンも。

１行に長ったらしいバージョンとくらべて、
どっちが作成しやすいでしょうか。
どっちが理解しやすいでしょうか。 

 //クエリ；fn西暦年抽出
 (和暦文字 as text) as text =>
 let
    元号リスト = {"M","T","S","H","R"},
    元号取得 = 元号リスト{Number.From(Text.Start(和暦文字,1)) - 1},
   和暦年 = 元号取得 & Text.Middle(和暦文字,1,2) & "年",
   西暦年 = Text.From(Date.Year(Date.From(和暦年)))
 in
   西暦年

上記のカスタム関数を利用するとこんな感じ 

 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"和暦文字", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "西暦年（数字ではなく文字列）", each fn西暦年抽出([和暦文字]), type text)
 in
    追加されたカスタム

（マナ） 2022/08/21(日) 15:47

同様に 

 //クエリ；fn西暦年抽出2
 (和暦文字 as text) as text =>
 let
    補正値リスト = {1867,1911,1925,1988,2018},
    補正値取得 = 補正値リスト{Number.From(Text.Start(和暦文字,1)) - 1},
   和暦年 = Number.From(Text.Middle(和暦文字,1,2)),
   西暦年 = Text.From(補正値取得 + 和暦年)
 in
   西暦年

（マナ） 2022/08/21(日) 16:42

マナさま
ありがとうございます。
本当に勉強になります。
マクロ禁止令が出て１月、四苦八苦しながらパワークエリにかじりついてます。
（まっち） 2022/08/22(月) 09:25
おそらく、次の段階にステップアップするには
レコードとかリストの理解が必要な気がします。
（´・ω・`）から出された課題にトライしてみてはどうでしょうか。

（マナ） 2022/08/22(月) 22:42

がんばります！
（まっち） 2022/08/23(火) 10:08
コメント返信：

[ 一覧(最新更新順) ]

YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.