M查询几个例子

最新推荐文章于 2022-11-24 07:24:09 发布

incognito007

最新推荐文章于 2022-11-24 07:24:09 发布

阅读量1.1k

点赞数

分类专栏： EXCEL

本文链接：https://blog.csdn.net/sfrankzhang/article/details/8826776

版权

EXCEL 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

= Table.Group(Source, {"Column1"}, {{"Column2", each [Column2], type list}, {"Column3", each Text.Combine([Column2], " "), type text}})



(page) => let
 Source = Web.Page(Web.Contents(“http://XXX/page/” & Number.ToText(page))),
 Data0 = Source{0}[Data]
 in
 Data0

let
 Source = Table.Combine(List.Transform({1..10}, Query))
 in
 Source


[
 Source = Excel.CurrentWorkbook(){[Name="TABLE"]}[Content],
 SourceTable = Table.AddIndexColumn(Source,"RowNo",1),
 AddPayeeID = Table.FillDown(Table.AddColumn(SourceTable,"PayeeID", each if [Full Name] = null then null else [RowNo]),"PayeeID"),
 AddClaimID = Table.FillDown(Table.AddColumn(AddPayeeID,"ClaimID", each if [Claim Date] = null then null else [RowNo]),"ClaimID"),
 modSource = Table.FillDown(Table.AddColumn(AddClaimID,"ClaimItemID", each if [Start Date] = null then null else [RowNo]),"ClaimItemID"),
 PayeeTable = Table.SelectRows(Table.SelectColumns(modSource,{"Full Name","Grade","PayeeID"}), each [Full Name] <> null),
 ClaimsTable1 = Table.SelectRows(Table.SelectColumns(modSource,{"Claim Date","Payment Date","ClaimID","PayeeID","Status","Total Claimed","Amount Paid"}), each [Claim Date] <> null),
 ClaimsTable = Table.TransformColumns(ClaimsTable1,{{"Total Claimed",each if _ = "NULL" then 0 else _},{"Amount Paid",each if _ = "NULL" then 0 else _}}),
 ClaimsItemsTable = Table.SelectRows(Table.SelectColumns(modSource,{"Start Date","ClaimID","PayeeID","ClaimItemID","Expense Type","Euro Line Amount"}), each [Start Date] <> null),
 ClaimsItemsDetailsTable = Table.SelectRows(Table.SelectColumns(modSource,{"ClaimID","PayeeID","ClaimItemID","Quantity","Unit","Net Total (EUR)"}), each [#"Net Total (EUR)"] <> "NULL")
]




let
    days={1..31},
    months={1..12},
    years={2000..2010},
    monthLU={"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},
    dayT=Table.FromList(days,each {_},{"Day"}),
    monthT=Table.FromList(months,each {_},{"Month"}),
    yearT=Table.FromList(years,each {_},{"Year"}),
    dayJ = Table.AddColumn(dayT, "joiner", each 1),
    monthJ = Table.AddColumn(monthT, "joiner", each 1),
    yearJ = Table.AddColumn(yearT, "joiner", each 1),
    yearmonth=Table.Join(yearJ,"joiner",monthJ,"joiner"),
    yearmonthday=Table.Join(yearmonth,"joiner",dayJ,"joiner"),
    addDate=Table.AddColumn(yearmonthday,"Date",each let x = try #date([Year],[Month],[Day])
                                                     in if x[HasError]
                                                     then #date(1,1,1)
                                                     else x[Value]),
    removeErrors=Table.SelectRows(addDate,each not([Date]=#date(1,1,1))),
    cleanCols=Table.SelectColumns(removeErrors,{"Date","Year","Month"}),
    renameCols = Table.RenameColumns(cleanCols,{"Month","MonthNo"}),
    addtextDate = Table.AddColumn(renameCols,"TextDate",each Date.ToText([Date],"YYYYMMDD")),
    addQtr = Table.AddColumn(addtextDate,"Qtr",each "Qtr" & Text.From(Date.QuarterOfYear([Date]))),
    DateDim = Table.AddColumn(addQtr,"Month",each monthLU{[MonthNo]-1})
in
    DateDim