1. ã¯ããã«
1.1. 誀解
Power Queryã䜿ã£ãŠãããšåºãŠãããã«ããŒã¯ãšãªã¯éªéã âŠãPower Queryã®ã«ã¹ã¿ã 颿°ã£ãŠãããã°ã§ããªãâŠããããªãµãã«èããŠããææã俺ã«ããããŸãããïŒãªããæšå¹Žãåãæžãåºãã ã£ãæš¡æ§âãŸãã§æé·ããŠããªãïŒ
1.2. æ£è§£
ãã«ã㌠ã¯ãšãªã¯è¯ããã€ã ããã«ã¹ã¿ã 颿°ã¯ãããã°ïŒä»ã®ã¯ãšãªã®ããã«åã¹ãããã®çµæã確èªå¯èœïŒã§ããã
- ãã«ã㌠ã¯ãšãªã«ã€ããŠ
- usdemy @éŽæšã²ã§ãã - ExcelïŒ Power BIäž¡æ¹ã§äœ¿ããPower Query å®å šã¬ã€ããåçŽè ããäžçŽè ãŸã§å¯Ÿå¿
- YouTube @Excelãã«ã¿CH - ãã®äžã«ãã«ããŒã¯ãšãªãéªéè ãã€ããããŠãã人ããããæãäžããŠãã ãããExcel PowerBIã
- note @Excelãã«ã¿ã®æžããã® - ãªã«ããšéªéè ãã€ããããããã«ããŒã¯ãšãªãå®ã¯æèœããããExcel PowerQuery PowerBIã
- ã«ã¹ã¿ã 颿°ã«ã€ããŠ
ãPower Query ã«ã¹ã¿ã 颿°ãã§ã°ã°ã£ãŠæåã«ãããããã®ã¯MSã®ããŒãžã ããæåãããã®ãšããã«ããã°è¯ãã£ããã ãã©ãçžå€ãããMSã®ããŒãžã¯èªã¿ã«ããã®ã§ã¡ãããšèªãã§ãªãã£ãã
æ¬èšäºã§ã¯èªåãªãã«èª¿ã¹ãããšãå ãã€ã€èª¬æãããããæ å ±ã£ãŠãç¥ã£ãŠã人ã¯ç¥ã£ãŠãããã ãããã©ããŸãšãŸã£ãèšäºãèŠã€ããããªãã£ãã®ã§äœæããŠã¿ãã
1.3. å ã«ãŸãšã
- ãã«ã㌠ã¯ãšãªã«ã€ããŠ
-
ãµã³ãã« ãã¡ã€ã«ã®å€æã¯ãšãªã¯ãã¡ã€ã«ã®å€æé¢æ°ãšãªã³ã¯ããŠãã - ãã®ããã
ãµã³ãã« ãã¡ã€ã«ã®å€æã¯ãšãªãç·šéããã°ããµã³ãã« ãã¡ã€ã«ä»¥å€ã®ãã¡ã€ã«ã«ãåã倿ãé©çšããã
-
- ã«ã¹ã¿ã 颿°ã«ã€ããŠ
- ãããã°å¯èœãªã«ã¹ã¿ã 颿°ã®äœææ¹æ³
- ãã©ã¡ãŒã¿ãŒãäœæ: ãã©ã¡ãŒã¿ãŒãäœæããåŒæ°ã«ãããå€ïŒor ã¯ãšãªïŒãåç §
- ã«ã¹ã¿ã 颿°äœæçšã¯ãšãªãäœæ: ãã®ãã©ã¡ãŒã¿ãŒãåç §ããã¯ãšãªãäœæãã«ã¹ã¿ã 颿°ã§è¡ãããåŠçãèšå®ã
- ã«ã¹ã¿ã 颿°ãäœæ: ã«ã¹ã¿ã 颿°äœæçšã¯ãšãªãå³ã¯ãªãã¯ã [颿°ã®äœæ] ã§ããã®ã¯ãšãªã«ãªã³ã¯ããã«ã¹ã¿ã 颿°ãäœæã
- ãããã°æ¹æ³: ãã©ã¡ãŒã¿ãŒã«ã»ããããå€ãå€ããã°ãã«ã¹ã¿ã 颿°äœæçšã¯ãšãªã®åã¹ãããã®çµæã確èªå¯èœ
- ã«ã¹ã¿ã 颿°ã®åŒæ°
- åŒæ°ã¯è€æ°èšå®å¯èœãã«ã¹ã¿ã 颿°äœæçšã¯ãšãªã§è€æ°ã®ãã©ã¡ãŒã¿ãŒãåç §ããã°ããã
- 詳现ãšãã£ã¿ãŒã§çŽæ¥ç·šéããã°ãGUIã§èšå®ã§ããªãåïŒããŒãã«çïŒãåŒæ°ã«ã§ããããã®å ŽåãGUIã®ãã©ã¡ãŒã¿ãŒã®ç®¡çç»é¢ããšã©ãŒã§éãããªããªãã®ã§æ³šæããã ããMèšèªã®åäœäžã¯äœã®åé¡ããªã(https://bengribaudo.com/blog/2021/03/17/5523/power-query-m-primer-part20-metadata) ããããšæãã
- ããã¥ã¡ã³ãäœæ: ããã¥ã¡ã³ãã¯é¢æ°ã®åã®ã¡ã¿ããŒã¿ãšããŠèšèŒããããã®ã¡ã¿ããŒã¿ä»ãã®åã颿°ã«ã»ããããã°ãããã¥ã¡ã³ãä»ãã®ã«ã¹ã¿ã 颿°ãäœæã§ããã
- ãããã°å¯èœãªã«ã¹ã¿ã 颿°ã®äœææ¹æ³
2. ãã«ã㌠ã¯ãšãªã¯è¯ããã€ãšãã話
ãã«ã㌠ã¯ãšãªãšã¯ãGUIã§ã¯ãšãªãäœæããéã«ããŸã«åºãããªãããã¡ããã¡ããããã€ãâã¿ãããªæãã

ããŒã¿ãååŸããã¯ãšãªã1ã€äœãã€ããã5ã€ãçæãããŠããŸãããã¡ããã¡ãããããèªãã®ãé¢åã ããäœ¿ãæ¹ãåãããªãããæ¶ããªããã§æ¬é ããŠããŸããã¡ããããŠããããäžã§ã¯æ¥æ¬èªã«éãããããåé¿ããæ¹æ³ã玹ä»ãããŠãããããã
ã ããããããã«ã㌠ã¯ãšãªã¯å®ã¯å¯èœæ§ã®ç£ã ã£ãããã
2.1. ãã«ã㌠ã¯ãšãªåºçŸ
ãŠãŒã¹ã±ãŒã¹: ãã©ã«ããŒå ã«å«ãŸãããããŒã¿åœ¢åŒãåãè€æ°ã®ãã¡ã€ã«ã«å¯ŸããããŒã¿ã倿ãã€ã€äžã€ã®ããŒãã«ã«çµåã
e-statã®æŒæ¥ã»é€æ®æ¥çç£çµ±èšå¹Žå ±ãããæµ·é¢æŒæ¥éçš®å¥æŒç²é环幎統èšïŒéœéåºçå¥ïŒã®åçã®ããŒã¿ïŒ.xlx圢åŒðïŒã䜿ã£ãŠèª¬æã.xlxãã¡ã€ã«ã䜿ãã®ã¯æ¬èšäºèª¬æã®ãããªã®ã§ãå®éã«e-statã®ããŒã¿ã䜿ãå Žåã¯APIãªãã䜿ããŸãããïŒäœ¿ãæ¹ç¥ãããã©ïŒã
ãŸãã¯ä»¥äžã®ãã¡ã€ã«ãããŒã«ã«ã«ä¿åã
- åæµ·é(æå31幎ïœå¹³æ27幎)
- 鿣®(æå31幎ïœå¹³æ27幎)
- 岩æ(æå31幎ïœå¹³æ27幎)
ããŒã¿ã®äžèº«ã¯ãããªæããããŒãããã®ããŒã¿ããã

ãã£ããPower BIã«åã蟌ãã§ã¿ããPower BIã® [ããŒã¿ãååŸ] -> [詳现âŠ] ãã [ãã©ã«ããŒ] ãéžæ:
å ã»ã©ã®ãã¡ã€ã«ãä¿åãããã¹ãæå®ãã[çµå] ã®ãã«ããŠã³ãã [ããŒã¿ã®çµåãšå€æ] ãéžæ:
ãµã³ãã« ãã¡ã€ã«ãšååŸå¯Ÿè±¡ã®ã·ãŒãïŒååšããå Žåã¯ããŒãã«ïŒãæå®:
ãã«ã㌠ã¯ãšãªãåºçŸãPower Query ãšãã£ã¿ãŒããã¡ããã¡ããã('Ï')
èªåçã«äœæãããã¯ãšãªã¯ãã¡ããã¡ãã§èªãã®ãé¢åã ãããã®ã±ãŒã¹ã§ã¯ããŒã¿ããŸãšãã«å€æã§ããŠããªãããããšããããããŒã¿ã®ååŸããããçŽããŠã¿ãããªããã
2.2. ãµã³ãã« ãã¡ã€ã«ã®å€æã¯ãšãªã䜿ã£ãŠã¿ã
å®ã¯ã倿ãçŽãã ããªããµã³ãã« ãã¡ã€ã«ã®å€æã¯ãšãªãä¿®æ£ããã ãã§OKããµã³ãã« ãã¡ã€ã«ã®å€æã¯ãšãªã詳现ãšãã£ã¿ãŒã§éãã以äžã貌ãä»ããã
let
ãœãŒã¹ = Excel.Workbook(ãã©ã¡ãŒã¿ãŒ2, null, true),
Sheet1 = ãœãŒã¹{[Name="Sheet1"]}[Data],
éœéåºç = Sheet1[Column1]{3},
äžèŠè¡åé€ = Table.Skip(Sheet1,4),
äžèŠè¡åé€1 = Table.AlternateRows(äžèŠè¡åé€,3,1,1000000),
ã€ã³ããã¯ã¹äœæ = Table.FirstN(äžèŠè¡åé€1,3),
ã€ã³ããã¯ã¹äœæ_äžæ¹åãžã³ããŒæžã¿ = Table.FillDown(ã€ã³ããã¯ã¹äœæ, Table.ColumnNames(ã€ã³ããã¯ã¹äœæ)),
ã€ã³ããã¯ã¹äœæ_転眮ãããããŒãã« = Table.Transpose(ã€ã³ããã¯ã¹äœæ_äžæ¹åãžã³ããŒæžã¿),
ã€ã³ããã¯ã¹äœæ_äžæ¹åãžã³ããŒæžã¿1 = Table.FillDown(ã€ã³ããã¯ã¹äœæ_転眮ãããããŒãã«,{"Column1", "Column2", "Column3"}),
ã€ã³ããã¯ã¹äœæ_宿 = Table.Transpose(ã€ã³ããã¯ã¹äœæ_äžæ¹åãžã³ããŒæžã¿1),
å€è¡äœæ_åé€ãããæåã®è¡1 = Table.Skip(äžèŠè¡åé€,3),
å€è¡äœæ_ãã£ã«ã¿ãŒãããè¡ = Table.SelectRows(å€è¡äœæ_åé€ãããæåã®è¡1, each [Column1] <> null and [Column1] <> ""),
ã€ã³ããã¯ã¹ãšå€çµ±å = Table.Combine({ã€ã³ããã¯ã¹äœæ_宿, å€è¡äœæ_ãã£ã«ã¿ãŒãããè¡}),
転眮ãããããŒãã«1 = Table.Transpose(ã€ã³ããã¯ã¹ãšå€çµ±å),
倿Žãããå = Table.TransformColumnTypes(転眮ãããããŒãã«1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
ææ ŒãããããããŒæ° = Table.PromoteHeaders(倿Žãããå, [PromoteAllScalars=true]),
#"ååã倿Žãããå " = Table.RenameColumns(ææ ŒãããããããŒæ°,{{"幎次", "åé¡1"}, {"幎次_1", "åé¡2"}, {"幎次_2", "åé¡3"}}),
ããããè§£é€ãããä»ã®å = Table.UnpivotOtherColumns(#"ååã倿Žãããå ", {"åé¡3", "åé¡2", "åé¡1"}, "幎", "æŒç²é"),
æŒç²éæ¬ æå€ãnullã« = Table.ReplaceValue(ããããè§£é€ãããä»ã®å, each if List.Contains({"⊠", "x ", "- "},[æŒç²é]) then [æŒç²é] else false, null,Replacer.ReplaceValue, {"æŒç²é"}),
æŒç²éæ¬ æå€ãnullã«_倿Žãããå1 = Table.TransformColumnTypes(æŒç²éæ¬ æå€ãnullã«,{{"æŒç²é", Int64.Type}}),
å¹Žå€æŽ = Table.TransformColumns(æŒç²éæ¬ æå€ãnullã«_倿Žãããå1, {"幎", each Text.Middle(_, Text.PositionOf(_, "(")+1,4)}),
å¹Žå€æŽ_倿Žãããå1 = Table.TransformColumnTypes(å¹Žå€æŽ,{{"幎", type number}}),
éœéåºçå远å = Table.AddColumn(å¹Žå€æŽ_倿Žãããå1, "éœéåºç", each éœéåºç, type text),
äžŠã¹æ¿ããããå = Table.ReorderColumns(éœéåºçå远å ,{"幎", "éœéåºç", "åé¡1", "åé¡2", "åé¡3", "æŒç²é"})
in
äžŠã¹æ¿ããããå
ãããšããµã³ãã« ãã¡ã€ã«ã®å€æã¯ãšãªãã¡ãããšæŽç¶ããŒã¿ã«å€æãããã

âŠã ããããªããsourceã¯ãšãªãèŠãŠã¿ããšã ãµã³ãã« ãã¡ã€ã«ä»¥å€ã®ãã¡ã€ã«ã«ãåã倿ãé©çšãããããŒãã«ãçµåãããŠããïŒïŒ ãã«ã㌠ã¯ãšãªãè¯ããã€ããã(*^^)
3. ã«ã¹ã¿ã 颿°ããããã°ãã
å®ã¯ããã«ã㌠ã¯ãšãªã¯ã«ã¹ã¿ã 颿°ãèªåã§äœãä»çµã¿ããªã®ã§ãäžã§ãã£ãããšã¯ã«ã¹ã¿ã 颿°ã®ãããã°ãšãèšããããã«ã㌠ã¯ãšãªã®ä»çµã¿ãçè§£ããŠã«ã¹ã¿ã 颿°ãäœæããã°ããããã°ã§ãããšããããšã
3.1. ã¡ãã£ãšå¯ãé
èªã¿é£ã°ããŠãOKã ãã©ãããPower Queryã®çè§£ãæ·±ãŸãã
颿°ã®åºç€
以äžãåè
- Qiita @spumoni - Mããå§ããã #6 ãPower Queryã®é¢æ°ãäœæãã
- Ben Gribaudo - Power Query M Primer (Part 2): Functions: Defining
- Ben Gribaudo - Power Query M Primer (Part 3): Functions: Function Values, Passing, Returning, Defining Inline, Recursion
äŸãã°ããµãã€ã®æ°å€aãšbã®åèšã®2åãè¿ã颿°ãå®çŸ©ããã«ã¯ä»¥äžïŒ
(a as number, b as number) as number =>
let
ret = 2 * (a + b)
in
ret
ç°¡åãªèª¬æïŒ
- æåã®æ¬åŒ§: åŒæ°ãšãã®åïŒåã¯æå®ããªããŠãå¯ïŒ
- ãã®æ¬åŒ§ã®ããåŸ: æ»ãå€ã®å
- =>以é: æ»ãå€
空ã®ã¯ãšãªãdoubleSumããäœæãã詳现ãšãã£ã¿ãŒããäžèšã貌ãä»ããã°ãPower Queryå
ã§ã«ã¹ã¿ã 颿°ãšããŠäœ¿çšå¯èœã«ãªãã颿°åã¯ã¯ãšãªåã

ãã©ã¡ãŒã¿ãŒãšã¡ã¿ããŒã¿ã®åºç€
以äžãåèïŒ
- Ben Gribaudo - Power Query M Primer (Part 20): Metadata
- Qiita
@tanuki_phoenix - MetadataãããŸã«ã¯äœ¿ã£ãŠã¿ã
ãã©ã¡ãŒã¿ãŒã¯å€ã«ç¹æ®ãªã¡ã¿ããŒã¿ãã»ãããããã¯ãšãªã詊ãã«ç©ºã®ã¯ãšãªãäœæãã詳现ãšãã£ã¿ãŒã§ä»¥äžãšããïŒ
"hoge" meta [IsParameterQuery=true, Type="Number"]
ãããšã[ãã©ã¡ãŒã¿ãŒã®ç®¡ç]ã«ãã®ã¯ãšãªã远å ãããã
Type=ã®å³èŸºã«èšå®å¯èœãªïŒãã¹ãç°å¢ãèªèããïŒåã¯ä»¥äžãã«ã¹ã¿ã 颿°ã§äœ¿ããåãšåãããšæããããTableãListãå«ãŸããŠããªãïŒãªãã ïŒïŒïŒã
| å | å€ |
|---|---|
| ãã¹ãŠ | "Any" |
| 10鲿° | "Number" |
| æ¥ä»/æå» | "DateTime" |
| æ¥ä» | "Date" |
| æå» | "Time" |
| æ¥ä»/æå»/ã¿ã€ã ãŸãŒã³ | "DateTimeZone" |
| æé | "Duration" |
| ããã¹ã | "Text" |
| True/False | "Logical" |
| ãã€ã㪠| "Binary" |
ã¡ã¿ããŒã¿ã¯MèšèªåŽã§ã¯æå³ããªããã®ã§ã¯ãããããã¹ãç°å¢ïŒPower BIãExcelïŒåŽããå©çšããããã®ããã©ã¡ãŒã¿ãŒã®ç®¡çããã®äŸããã©ã¡ãŒã¿ãŒã«éããããã¹ãç°å¢åŽã¯Mèšèªã®å 容ã«å¿ããŠè¡šç€ºãå€ãããããããããã²ãŒã·ã§ã³ãã¹ãããã®ããã«ã
3.2. ãã©ã¡ãŒã¿ãŒã䜿çšããŠã«ã¹ã¿ã 颿°ãäœæãã
learn.microsoft - ã«ã¹ã¿ã 颿°ã®äœ¿çšã®å 容ããå°ãã¯ã¿åºã€ã€ãã«ã¹ã¿ã 颿°ãäœæãããã
ããŒã¿ ãœãŒã¹ã¯å ã»ã©ãšåæ§ãe-statã®ããŒã¿ã䜿çšãããæ°ãã.pbixãã¡ã€ã«ãäœæããã¯ãšãª ãšãã£ã¿ãŒãéãã以äžã®4ã€ã®ã¯ãšãªãäœæïŒ[空ã®ã¯ãšãª] ãã詳现ãšãã£ã¿ãŒã§çŽæ¥å ¥åïŒãåã¯ãšãªãšã [èªã¿èŸŒã¿ãæå¹ã«ãã] ã®ãã§ãã¯ãå€ããŠããã
let
folderPath = "path to folder", //é©å®æžãæã
ãœãŒã¹ = Folder.Contents(folderPath),
WorkBookå远å = Table.AddColumn(ãœãŒã¹, "Workbook", each Excel.Workbook([Content]), type table)
in
WorkBookå远å
let
fileName = "c001c-005-002-000-000.xls",
ãœãŒã¹ = ãã¡ã€ã«äžèЧ{[Name=fileName]}[Workbook]
in
ãœãŒã¹
sampleWorkbook meta [IsParameterQuery=true, Type="Table", IsParameterQueryRequired=true]
let
ãœãŒã¹ = argWorkbook,
Sheet1 = ãœãŒã¹{[Name="Sheet1"]}[Data],
éœéåºç = Sheet1[Column1]{3},
äžèŠè¡åé€ = Table.Skip(Sheet1,4),
äžèŠè¡åé€1 = Table.AlternateRows(äžèŠè¡åé€,3,1,1000000),
ã€ã³ããã¯ã¹äœæ = Table.FirstN(äžèŠè¡åé€1,3),
ã€ã³ããã¯ã¹äœæ_äžæ¹åãžã³ããŒæžã¿ = Table.FillDown(ã€ã³ããã¯ã¹äœæ, Table.ColumnNames(ã€ã³ããã¯ã¹äœæ)),
ã€ã³ããã¯ã¹äœæ_転眮ãããããŒãã« = Table.Transpose(ã€ã³ããã¯ã¹äœæ_äžæ¹åãžã³ããŒæžã¿),
ã€ã³ããã¯ã¹äœæ_äžæ¹åãžã³ããŒæžã¿1 = Table.FillDown(ã€ã³ããã¯ã¹äœæ_転眮ãããããŒãã«,{"Column1", "Column2", "Column3"}),
ã€ã³ããã¯ã¹äœæ_宿 = Table.Transpose(ã€ã³ããã¯ã¹äœæ_äžæ¹åãžã³ããŒæžã¿1),
å€è¡äœæ_åé€ãããæåã®è¡1 = Table.Skip(äžèŠè¡åé€,3),
å€è¡äœæ_ãã£ã«ã¿ãŒãããè¡ = Table.SelectRows(å€è¡äœæ_åé€ãããæåã®è¡1, each [Column1] <> null and [Column1] <> ""),
ã€ã³ããã¯ã¹ãšå€çµ±å = Table.Combine({ã€ã³ããã¯ã¹äœæ_宿, å€è¡äœæ_ãã£ã«ã¿ãŒãããè¡}),
転眮ãããããŒãã«1 = Table.Transpose(ã€ã³ããã¯ã¹ãšå€çµ±å),
倿Žãããå = Table.TransformColumnTypes(転眮ãããããŒãã«1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
ææ ŒãããããããŒæ° = Table.PromoteHeaders(倿Žãããå, [PromoteAllScalars=true]),
#"ååã倿Žãããå " = Table.RenameColumns(ææ ŒãããããããŒæ°,{{"幎次", "åé¡1"}, {"幎次_1", "åé¡2"}, {"幎次_2", "åé¡3"}}),
ããããè§£é€ãããä»ã®å = Table.UnpivotOtherColumns(#"ååã倿Žãããå ", {"åé¡3", "åé¡2", "åé¡1"}, "幎", "æŒç²é"),
æŒç²éæ¬ æå€ãnullã« = Table.ReplaceValue(ããããè§£é€ãããä»ã®å, each if List.Contains({"⊠", "x ", "- "},[æŒç²é]) then [æŒç²é] else false, null,Replacer.ReplaceValue, {"æŒç²é"}),
æŒç²éæ¬ æå€ãnullã«_倿Žãããå1 = Table.TransformColumnTypes(æŒç²éæ¬ æå€ãnullã«,{{"æŒç²é", Int64.Type}}),
å¹Žå€æŽ = Table.TransformColumns(æŒç²éæ¬ æå€ãnullã«_倿Žãããå1, {"幎", each Text.Middle(_, Text.PositionOf(_, "(")+1,4)}),
å¹Žå€æŽ_倿Žãããå1 = Table.TransformColumnTypes(å¹Žå€æŽ,{{"幎", type number}}),
éœéåºçå远å = Table.AddColumn(å¹Žå€æŽ_倿Žãããå1, "éœéåºç", each éœéåºç, type text),
äžŠã¹æ¿ããããå = Table.ReorderColumns(éœéåºçå远å ,{"幎", "éœéåºç", "åé¡1", "åé¡2", "åé¡3", "æŒç²é"})
in
äžŠã¹æ¿ããããå
4ã€ç®ã¯å
ã»ã©äœæãããµã³ãã« ãã¡ã€ã«ã®å€æã¯ãšãªã®1è¡ç®ãå€ããã ãã
ããŒã¿å€æé¢æ°äœæçšã¯ãšãªãå³ã¯ãªãã¯ãã[颿°ã®äœæ] ãã¯ãªãã¯:

颿°åã¯ããŒã¿å€æé¢æ°ãšããã

ã«ã¹ã¿ã 颿°ãäœæãããšãããã€ãã®ã¯ãšãªãèªåçã«ã°ã«ãŒãåãããã

ãã€ãŸãããã«ã㌠ã¯ãšãªã¯âŠâŠãããäžé£ã®æµããèªåçæããä»çµã¿ã ã£ããã ãïŒïŒã
ïŒããŒãŒãŒã³ïŒïŒïŒ
Ω ΩΩïŒããªâŠâŠãªãã ã£ãŠãŒãŒãŒïŒïŒã
äœæããããŒã¿å€æé¢æ°ã«ã¹ã¿ã 颿°ã䜿ã£ãŠã¿ããã以äžã®ã¯ãšãªã远å ãããã®é¢æ°ãåŒã³åºããŠããŒã¿ãçµåã
let
ãœãŒã¹ = ãã¡ã€ã«äžèЧ,
ããŒã¿å€æé¢æ°åŒã³åºã = Table.AddColumn(ãœãŒã¹, "倿åŸ", each ããŒã¿å€æé¢æ°([Workbook]), type table),
ããŒãã«çµå = Table.Combine(ããŒã¿å€æé¢æ°åŒã³åºã[倿åŸ])
in
ããŒãã«çµå
ãããšããã«ã㌠ã¯ãšãªã®å ŽåãšåãçµæãåŸãããã
ãªããã¯ãšãªã®ã°ã«ãŒãåã䜿çšããŠèŠãç®ãæŽããŠãããšåŸã®äººïŒææ¥ã®èªåãå«ãïŒãèŠãããïŒ
3.3. ã«ã¹ã¿ã 颿°ã®ãããã°
ããŒã¿å€æé¢æ°ã«ã¹ã¿ã 颿°ã¯ããŒã¿å€æé¢æ°äœæçšã¯ãšãªãšãªã³ã¯ããŠãããããåã¹ãããã確èªããããšã§ãããã°å¯èœã
察象ã®Excelãã¡ã€ã«ã倿Žããã«ã¯ãsampleWorkbookã¯ãšãªã®åç
§å
ãããããã°ãããExcelãã¡ã€ã«ã«å€æŽããããã®äžã§ãããŒã¿å€æé¢æ°äœæçšã¯ãšãªã®åã¹ãããã確èªããããã¡ã€ã«ã§ã¯äžæãè¡ã£ããã©ãä»ã®ãã¡ã€ã«ã§ã¯ãã¡ã ã£ããšããã®ã¯ãããã話ã
ãµã³ãã« ãã¡ã€ã«ãåæµ·éã®ãã¡ã€ã«ãã鿣®ã®ãã¡ã€ã«ã«å€æŽããŠã¿ãããsampleWorkbookã¯ãšãªã以äžã®ããã«å€æŽãã:
let
fileName = "c001c-005-003-000-000.xls",
ãœãŒã¹ = ãã¡ã€ã«äžèЧ{[Name=fileName]}[Workbook]
in
ãœãŒã¹
ãããšãããŒã¿å€æé¢æ°äœæçšã¯ãšãªã®çµæã鿣®ã®ãã®ã«å€æŽããã:

ãã®äŸã§ã¯åé¡ãªã倿ã§ããŠããããäžæãè¡ã£ãŠããªãå Žåã¯ããŒã¿å€æé¢æ°äœæçšã¯ãšãªã®åã¹ãããã確èªããããšã
3.4. 泚æ
äžèšã§èšå®ããargWorkbookãã©ã¡ãŒã¿ãŒã®ããã§ã [ãã©ã¡ãŒã¿ãŒã®ç®¡ç] ç»é¢ã§ãšã©ãŒã衚瀺ããã[OK] ãã¿ã³ã§éããããªããªããããã¯Mèšèªã®ãã¹ãç°å¢ïŒPower BIïŒåŽããã©ã¡ãŒã¿ãŒã®å€ãšããŠããŒãã«ãæ³å®ããŠããªããããMèšèªåŽã«ã¯åé¡ãªãããã¶ãïŒé¢æ°ã®äœæã¯ãã¹ãç°å¢ãæäŸããŠãããããïŒãæ°ã«ãªããªããsampleWorkbookã¯ãšãªã®åç
§å
ããã€ããªãŒ ãã¡ã€ã«ã«å€æŽããïŒäœµããŠãããŒã¿å€æé¢æ°äœæçšã¯ãšãªã®2è¡ç®ã ãœãŒã¹ = Excel.Workbook(argWorkbook) ã«å€æŽããïŒã
3.5. åŒæ°ã2ã€ä»¥äžã«ãã
ãã©ã¡ãŒã¿ãŒ ã¯ãšãªãæ°ããäœæããã«ã¹ã¿ã 颿°ã®å ã«ãªã£ãã¯ãšãªã§åç §ãããåŒæ°ã®åã®èšå®æ¹æ³ã¯ã¡ã¿ããŒã¿ã®èª¬æã§è¿°ã¹ããšããã
äŸãã°ããã©ã«ããŒå ã«å«ãŸãããããŒã¿åœ¢åŒãåãè€æ°ã®ãã¡ã€ã«ã«å¯Ÿãããã¡ã€ã«å ã®è€æ°ã®ããŒãã«ãååŸãããããã1ã€ã®ããŒãã«ã«çµåãããå Žåããã®ã±ãŒã¹ã§ã¯ãããŒãã«ã®ååŸåŠçèªäœã¯å ±éãªã®ã§ãã¯ãŒã¯ããã¯ãããŒãã«åãããŒã¿ã®å倿ã®3ã€ãåŒæ°ãšããã«ã¹ã¿ã 颿°ã®å©çšãèããããã
ïŒãã®èšäºãã ãã¶é·ããªã£ã¡ãã£ãŠæžãã®ãç²ããŠãããã§ããããã¡ãã£ãšãããããã°ã£ãŠã¿ããããããïŒ
ææ¥ããããã°ãããããªãã»ã»ã»ã»ã»ã»
仿¥ã»ã»ã»
仿¥ã ãããã°ããã ã£ã»ã»ã»ã»ã»ã»ïŒ
仿¥ãããã°ã£ãè
ã»ã»ã»
仿¥ãããã°ãå§ããè
ã«ã®ã¿ã»ã»ã»
ææ¥ãæ¥ããã ãã»ã»ã»ïŒ
âââ çŠæ¬äŒžè¡ãè³åç Žæé²ã«ã€ãžãïŒè¬è«ç€ŸïŒããã倧槻çé·
以äžã®ããŒã¿ãå«ãExcelãã¡ã€ã«ãäœæãããŒã¿ã¯ã¡ãããšããŒãã«ã«ããŠãããã¡ã€ã«ã¯3ã€äœæãããã¡ã€ã«åã¯sample1.xlsxãsample2.xlsxãsample3.xlsxãšããã
ããŒãã«å: T.Table1
| A | B | C |
|---|---|---|
| 1 | a | 1:00 |
| 2 | b | 2:00 |
| 3 | c | 3:00 |
ããŒãã«å: T.Table2
| D | E | F |
|---|---|---|
| 〠| 1,000 | 2022/12/1 |
| ã | 2,000 | 2022/12/2 |
| ã | 3,000 | 2022/12/3 |
æ°ãã.pbixãã¡ã€ã«ãäœæããã¯ãšãªãšãã£ã¿ãŒãéãã以äžã®8ã€ã®ã¯ãšãªãäœæïŒ[空ã®ã¯ãšãª] ãã詳现ãšãã£ã¿ãŒã§çŽæ¥å ¥åïŒãåã¯ãšãªãšã [èªã¿èŸŒã¿ãæå¹ã«ãã] ã®ãã§ãã¯ãå€ããŠããã
let
folderPath = "path to folder", //é©å®æžãæã
ãœãŒã¹ = Folder.Contents(folderPath),
WorkBookå远å = Table.AddColumn(ãœãŒã¹, "Workbook", each Excel.Workbook([Content]), type table)
in
WorkBookå远å
let
fileName = "sample1.xlsx",
ãœãŒã¹ = ãã¡ã€ã«äžèЧ{[Name=fileName]}[Workbook]
in
ãœãŒã¹
"T.Table1"
{{"A", Int64.Type}, {"B", type text}, {"C", type time}}
sampleWorkbook meta [IsParameterQuery=true, Type="Table", IsParameterQueryRequired=true]
sampleTableName meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]
sampleTypeTransformation meta [IsParameterQuery=true, Type="List", IsParameterQueryRequired=true]
let
ãœãŒã¹ = argWorkbook,
T_Table = ãœãŒã¹{[Item=argTableName, Kind="Table"]}[Data],
倿Žãããå = Table.TransformColumnTypes(T_Table, argTypeTransformation)
in
倿Žãããå
ããŒã¿å€æé¢æ°äœæçšã¯ãšãªãå³ã¯ãªãã¯ãã[颿°ã®äœæ] ãã¯ãªãã¯ã颿°åã¯ããŒã¿å€æé¢æ°ãšããã
äœæããããŒã¿å€æé¢æ°ã«ã¹ã¿ã 颿°ãå©çšããŠãT.Table1ããŒãã«ãT.Table2ããŒãã«ããããŒã¿ãååŸãçµåããã«ã¯ã以äžã®ã¯ãšãªã§ã
let
tableName = "T.Table1",
typeTransformation = {{"A", Int64.Type}, {"B", type text}, {"C", type time}},
ãœãŒã¹ = ãã¡ã€ã«äžèЧ,
ããŒã¿å€æé¢æ°åŒã³åºã = Table.AddColumn(ãœãŒã¹, "Table", each ããŒã¿å€æé¢æ°([Workbook], tableName, typeTransformation)),
ããŒãã«çµå = Table.Combine(ããŒã¿å€æé¢æ°åŒã³åºã[Table])
in
ããŒãã«çµå
let
tableName = "T.Table2",
typeTransformation = {{"D", type text}, {"E", type number}, {"F", type date}},
ãœãŒã¹ = ãã¡ã€ã«äžèЧ,
ããŒã¿å€æé¢æ°åŒã³åºã = Table.AddColumn(ãœãŒã¹, "Table", each ããŒã¿å€æé¢æ°([Workbook], tableName, typeTransformation)),
ããŒãã«çµå = Table.Combine(ããŒã¿å€æé¢æ°åŒã³åºã[Table])
in
ããŒãã«çµå
ãã©ã¡ãŒã¿ãŒåããŠãããããäž¡ã¯ãšãªã®éã㯠tableName ãš typeTransformation ã®ã¿ã
çµæã¯ãããªæãã«ãªããã°ã«ãŒãåã§ã¯ãšãªãæŽããŠããããšãå¿ããã«ã

3.6. ããã¥ã¡ã³ãã远å ãã
ãã£ããèŠããã®ã§ãããã¥ã¡ã³ãã®è¿œå æ¹æ³ãç°¡åã«èª¬æãåŸæ¥ã詳ãã調ã¹ãŠå¥èšäºã«ãããããããªãããããªããããããªãã
- learn.microsoft - 颿°ããã¥ã¡ã³ãã®è¿œå
- Ben Gribaudo - Power Query M Primer (Part 20): Metadata
å
ã»ã©ã®äŸïŒåŒæ°ã2ã€ä»¥äžã«ããïŒã§ãããŒã¿å€æé¢æ°ãäœæãããã以äžã®ç»é¢ã®ãšããã説æãäœã«ãç¡ãã

ãã®äžæ¹ãæšæºé¢æ°ã®å Žåã¯è©³ããããã¥ã¡ã³ãã衚瀺ãããã

æéã¯ããããã説æãäœããªããšãä»ã®äººã¯äœ¿ãã¥ããããæéãçµã€ãšèªåã§ãå¿ããŠããŸãã®ã§ããã¥ã¡ã³ãã远å ããŠã¿ããã以äžã®ã¯ãšãªã远å ããã°OKã
let
Type = type function (
argWorkbook as (type table meta
[
Documentation.FieldCaption = "workbook",
Documentation.SampleValues = {""}
]
),
argTableName as (type text meta[
Documentation.FieldCaption = "tableName",
Documentation.SampleValues = {"Excelã®ããŒãã«å"}
]),
argTypeTransformation as (type list meta[
Documentation.FieldCaption = "typeTransformation",
Documentation.SampleValues = {}
]))
as table meta [
Documentation.Name = "ããŒã¿å€æé¢æ°_docä»",
Documentation.LongDescription =
"ã¯ãŒã¯ããã¯ããããŒãã«ãååŸãå倿ãè¡ã颿°ã" &
"<ul>" &
"<li>" &
"<code>workbook</code> (table) : Excel.Workbookã®æ»ãå€ã" &
"</li>" &
"<li>" &
"<code>tableName</code> (text) : ååŸå¯Ÿè±¡ãšããããŒãã«ã®ååã" &
"</li>" &
"<li>" &
"<code>typeTransformation</code> (list) : ååŸããããŒãã«ã«å¯ŸããŠé©çšããããŒã¿å倿ã<code>TransformColumnTypes</code>ã®ç¬¬2åŒæ°ã" &
"</li>" &
"</ul>",
Documentation.Examples = {
[
Description = "äŸã®ã¿ã€ãã«",
Code = "ããŒã¿å€æé¢æ°_docä»(Excel.Workbook(File.Content(""path to xlfile""), ""T.Table1"", {{""A"", type number}, {""B"", type text}})",
Result = "âãšåããã®ãTable.TransformColumnTypes(Excel.Workbook(File.Contents(""path to xlfile"")){[Item=""T.Table1"",Kind=""Table""]}[Data],{{""A"", type number}, {""B"", type text}})"],
[Description = "äŸã¯ããã€ã§ãæžããã", Code = "颚ãèªãæããŸã", Result = "ããŸããããŸãããïŒ"]
}
],
RetypedFunction = Value.ReplaceType(ããŒã¿å€æé¢æ°, Type)
in
RetypedFunction
ãããšã次ã®ããã«ããã¥ã¡ã³ãã远å ãããŠãããããããªãã

ããããã£ãŠããªãã®ã§è©³ãã説æã¯ããªããããã€ã³ãã¯ããã¥ã¡ã³ãã¯é¢æ°ã®ã¡ã¿ããŒã¿ã§ã¯ãªãã颿°ã®åã®ã¡ã¿ããŒã¿ããçæããããšããããšããã®ãããã¡ã¿ããŒã¿ä»ãã®é¢æ°ã®åãå®çŸ©ããŠãã«ã¹ã¿ã 颿°ã®åããã®åã§çœ®ãæãããšãã2ã¹ããããå¿ èŠã«ãªãã
ããšãDocumentation.LongDescriptionã§é¢æ°ã®è©³çްãèšè¿°ãããããã®éšåã¯HTMLã¿ã°ã䜿ããããªã®ã§ããã®äŸã§ã¯<UL>ã¿ã°ã<LI>ã¿ã°ã䜿çšããŠããã倪åã«ããã«ã¯<B>ãæ¹è¡ããŠè€æ°è¡ã«ããã«ã¯<BR>ã䜿ãã°OKãããŸã詊ããŠããªãããä»ã«ãããããªã¿ã°ã䜿ãããã
ããã¥ã¡ã³ãäœææã«ã¯ãæšæºé¢æ°ã®ããã¥ã¡ã³ãèšèŒæ¹æ³ãåèã«ãªããäŸãã°ãSql.Databaseã®ããã¥ã¡ã³ããåèã«ããã«ã¯ã次ã®ããã«Value.Metadata(Value.Type())ã§ã¡ã¿ããŒã¿ãååŸããåèŠçŽ ã調ã¹ãã°ããã颿°ãå€ããã«ã¯ãfunc =ã®å³èŸºã倿Žããã
let
func = Sql.Database,
metaData = Value.Metadata(Value.Type(func))
in
metaData
æ¬ç·šã¯ä»¥äžããããªããããªèšäºãæåŸãŸã§èªãã§ãããŠããããšãããããŸããããã°ã£ãŠæžããã®ã§ããããðããŠããããšããããã§ãã
4. ãããã«
å®ã¯ãã®èšäºã¯
Microsoft Power BI Advent Calendar 2022ã®23æ¥ç®ã®ãã®ã§ããïŒä»æŽïŒã幎æ«ããããä»å¹Žã®æ¯ãè¿ããšæ¥å¹Žããããããšã§ãããã
4.1. ä»å¹Žã®æ¯ãè¿ã
å»å¹Žæ«ã«DAXãçè§£ã§ããŠãããPower BIã®ããŒã¿ ã¢ãã«ãã©ãäœãã¹ãããäœããã£ãŠã¯ãããªãããäžæ°ã«ããã£ãïŒæ°ãããïŒãããŒã¿ ã¢ããªã³ã°ãããäžã§ãDAXã¡ãžã£ãŒã®çè§£ã¯éèŠãè¶ éèŠãä»å¹Žã¯ãPower Queryã§List.Generateã䜿ã£ãé«åºŠãªåŠçãã§ããããã«ãªã£ãããã«ã¹ã¿ã 颿°ã®ãããã°ãã§ããããã«ãªã£ãïŒé¢ä¿ãªããã©æçž«ãã§ãºãã³ã®è£Ÿäžããã§ããããã«ãªã£ãïŒãã»ã«ããµãŒãã¹BIã«ã€ããŠã¯ããšã¯æ°ãããªãã ãããªã
4.2. æ¥å¹Žããããããš
æ¥å¹Žã¯ãšã³ã¿ãŒãã©ã€ãºBIãäœãšãããããçµç¹ãšããŠBIã®æ©æµãåããã«ã¯ããããã£ããããªããšãPower BIãæé©ãªææ®µãã¯ãŸã åãããªããã©ããšã³ã¿ãŒãã©ã€ãºBIãå°å ¥ã§ãããããããŒã¿åºç€ã®æ§ç¯æ¹æ³ãæ å ±ç®¡çã®æ¹æ³ã«ã€ããŠããŸãã¯å匷ããå§ãããã
ãã®ä»ãããŒã ãšããŠã®BIéçºæ¹æ³ã»ã«ãŒã«ããçµç¹å éçºè 逿ãå å»¶ã°ãã«ãªã£ãŠããDAXã¯ãšãªã®å匷ãšãã«åãçµãã§ã¿ãããšããã
æãããŠçµç¹ã«BIãæ ¹ä»ãããããšãã§ãããããæ¥å¹Žã®ããã°ã次第ãããã('A`)





