Power Query M - Code for Options for reading CSV or ZIP files

=============================================================

1º A seguir, segue uma forma mais "artesanal" de se juntar vários CSV em um dataset, através do script:
(The following is a more "handcrafted" way to merge multiple CSVs into one dataset through the script)

let
    Source201907 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201907.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source201906 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201906.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source201905 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201905.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source201904 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201904.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source201903 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201903.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source201902 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201902.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source201901 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201901.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source201812 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201812.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source201811 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201811.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source201810 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201810.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source201809 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201809.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source201808 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201808.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source201807 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201807.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source201806 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201806.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source201805 = Csv.Document(File.Contents("C:\Temp\datasets\csv\Sales201805.CSV"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),

    TabelaSource = Table.Combine({#"Source201907",
                                  #"Source201906",
                                  #"Source201905",
                                  #"Source201904",
                                  #"Source201903",
                                  #"Source201902",
                                  #"Source201901",
                                  #"Source201812",
                                  #"Source201811",
                                  #"Source201810",
                                  #"Source201809",
                                  #"Source201808",
                                  #"Source201807",
                                  #"Source201806",
                                  #"Source201805"                               
                                  }),
    #"Renamed Columns" = Table.RenameColumns(TabelaSource,{{"Column1", "DtVenda"}, {"Column2", "Moeda"}, {"Column5", "CodSku"}, {"Column9", "Postos"}, {"Column16", "VlrVenda"}, {"Column19", "VlrVendaLiq"}, {"Column17", "VlrIVA"}, {"Column18", "QtdSKUs"}, {"Column4", "CodSAP"}, {"Column15", "DtHora"}, {"Column6", ".Canal"}, {"Column7", ".SetorAtiv"}, {"Column8", ".Empresa"}, {"Column21", ".Attibut1"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",".",",",Replacer.ReplaceText,{"VlrVendaLiq"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".",",",Replacer.ReplaceText,{"VlrIVA"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".",",",Replacer.ReplaceText,{"QtdSKUs"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",".",",",Replacer.ReplaceText,{"VlrVenda"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value3",{{"VlrVenda", type number}, {"VlrIVA", type number}, {"QtdSKUs", type number}, {"VlrVendaLiq", type number}})
in
    #"Changed Type"

=============================================================

1º - Criar uma função chamada UnzipContents:
(Create a function called UnzipContents)

(ZIPFile) =>
let
    Header = BinaryFormat.Record([
        MiscHeader = BinaryFormat.Binary(14),
        BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        FileSize   = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
        ExtrasLen  = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
    ]),

    HeaderChoice = BinaryFormat.Choice(
        BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        each if _ <> 67324752             // not the IsValid number? then return a dummy formatter
            then BinaryFormat.Record([IsValid = false, Filename=null, Content=null])
            else BinaryFormat.Choice(
                    BinaryFormat.Binary(26),      // Header payload - 14+4+4+2+2
                    each BinaryFormat.Record([
                        IsValid  = true,
                        Filename = BinaryFormat.Text(Header(_)[FileNameLen]),
                        Extras   = BinaryFormat.Text(Header(_)[ExtrasLen]),
                        Content  = BinaryFormat.Transform(
                            BinaryFormat.Binary(Header(_)[BinarySize]),
                            (x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null
                        )
                        ]),
                        type binary                   // enable streaming
                )
    ),

    ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true),

    Entries = List.Transform(
        List.RemoveLastN( ZipFormat(ZIPFile), 1),
        (e) => [FileName = e[Filename], Content = e[Content] ]
    )
in
    Table.FromRecords(Entries)

2º Criar através de uma nova tabela (_Blank), através do script:
(Create through a new table (_Blank) through the script)

let
    Source = File.Contents("C:\Temp\datasets\csv\Sales2018.zip"),
    Files = UnzipContents(Source),
    #"Filtered Hidden Files1" = Table.SelectRows(Files, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", type text}, {"Column9", type text}, {"Column10", Int64.Type}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", type text}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", type number}, {"Column17", type number}, {"Column18", type number}, {"Column19", type number}, {"Column20", type text}, {"Column21", Int64.Type}})
in
    #"Changed Type"

Fonte:
http://sql10.blogspot.com/2016/06/reading-zip-files-in-powerquery-m.html

Power Query M - Code for creating a table DCalendar (Fiscal)

Code for creating a table Dim_Calendar Refresh

Path
let
    Source = "C:\Temp\datasets\board\csv\__20200210_120926_.ARCHIVE"
in
    Source
========================================
dCalend_FiscalYearEndMonthValue
let
    Source = 12
in
    Source
========================================
fnGetFiscalYear
let
    Source = (AnyDate as date) =>
    let
    FiscalYearStartingMonth = if dCalend_FiscalYearEndMonthValue = 12 then 1 else dCalend_FiscalYearEndMonthValue + 1,
    CurrentDate = AnyDate,
    CurrentMonth = Date.Month(CurrentDate),
    CurrentYear = Date.Year(CurrentDate),
    CurrentFiscalYear =
    if dCalend_FiscalYearEndMonthValue = 12 then CurrentYear else
    if CurrentMonth < FiscalYearStartingMonth then CurrentYear else
    CurrentYear + 1
    in
    CurrentFiscalYear
in
    Source
========================================
fnGetFiscalMonthYear
let
    Source = (AnyDate as date, month as number) =>
    let
    FiscalYearStartingMonth = if dCalend_FiscalYearEndMonthValue = 12 then 1 else dCalend_FiscalYearEndMonthValue + 1,
    CurrentDate = AnyDate,
    CurrentMonth = Date.Month(CurrentDate),
    CurrentYear = Date.Year(CurrentDate),
    CurrentQuarter = Date.QuarterOfYear(CurrentDate),
    CurrentFiscalYear = fnGetFiscalYear(CurrentDate),
    CurrentFiscalMonthYear =
    if dCalend_FiscalYearEndMonthValue = 12 then Text.From(CurrentFiscalYear*100 + CurrentMonth) else
    if CurrentMonth < FiscalYearStartingMonth then Text.From(CurrentFiscalYear*100 + 12 - month + CurrentMonth) else
    Text.From(CurrentFiscalYear*100 + CurrentMonth - month)
    in
    CurrentFiscalMonthYear
in
    Source
========================================
fnGetFiscalQuarterYear
let
    Source = (AnyDate as date, month as number) =>
    let
    FiscalYearStartingMonth = if dCalend_FiscalYearEndMonthValue = 12 then 1 else dCalend_FiscalYearEndMonthValue + 1,
    CurrentDate = AnyDate,
    CurrentMonth = Date.Month(CurrentDate),
    CurrentYear = Date.Year(CurrentDate),
    CurrentQuarter = Date.QuarterOfYear(CurrentDate),
    CurrentFiscalYear = fnGetFiscalYear(CurrentDate),
    CurrentFiscalQuarterYear =
    if dCalend_FiscalYearEndMonthValue = 12 then "Q" & Text.From(CurrentQuarter) & "-" & Text.From(CurrentFiscalYear) else
    if CurrentMonth < FiscalYearStartingMonth then "Q" & Text.From(Number.RoundUp((12 - month + CurrentMonth)/3)) & "-" & Text.From(CurrentFiscalYear) else
    "Q" & Text.From(Number.RoundUp((CurrentMonth - month)/3)) & "-" & Text.From(CurrentFiscalYear)
    in
    CurrentFiscalQuarterYear
in
    Source
========================================
fnGetPastDate
let
    Source = (numberOfDays as number) =>
let
    CurrentDate = DateTime.LocalNow(),
    RequiredDate = DateTimeZone.From(Date.AddDays(CurrentDate, numberOfDays * -1))
in
    RequiredDate
in
    Source
========================================
fnDateTable
let
    Source = (month as number) =>
let
    FiscalYearStartingMonth = if dCalend_FiscalYearEndMonthValue = 12 then 1 else dCalend_FiscalYearEndMonthValue + 1,
 
    CurrentDate = Date.FromText("2019-07-31"), //Ajustar para Produção
    //CurrentDate = Date.From(DateTime.LocalNow()),
    CurrentFiscalYear = fnGetFiscalYear(CurrentDate),
 
    FiscalStartDate = Date.From(Text.From(FiscalYearStartingMonth) & "/01/" & Text.From(CurrentFiscalYear)),
 
    StartDate = Date.From(Date.AddYears(FiscalStartDate,-1)), //Inicio do Calendario
    EndDate= Date.From(Date.AddYears(FiscalStartDate, 1)),    //Fim do Calendario

    //Create lists of month and day names for use later on
    MonthList = {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"},
    DayList = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"},

    //Find the number of days between the end date and the start date
    NumberOfDates = Duration.Days(EndDate-StartDate)+1,

    //Generate a continuous list of dates from the start date to the end date
    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),

    //Turn this list into a table
    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),

    //Caste the single column in the table to type date
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),

    //Add custom columns for day of month, month number, year
    DayOfMonth       = Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])),
    MonthNumber      = Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])),
    Year             = Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])),
    DayOfWeekNumber  = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1),
    QuarterOfYear    = Table.AddColumn(DayOfWeekNumber, "QuarterOfYear", each "Q" & Text.From(Date.QuarterOfYear([Date])) & "-" & Text.From([Year])),
    YYYYMMDD         = Table.AddColumn(QuarterOfYear, "YYYYMMDD", each Text.From(Date.Year([Date]))  & "" & Text.PadStart(Text.From(Date.Month([Date])), 2, "0")  & "" & Text.PadStart(Text.From(Date.Day([Date])), 2, "0")),
    YYYYMM           = Table.AddColumn(YYYYMMDD, "YYYYMM", each Text.From(Date.Year([Date]))  & "" & Text.PadStart(Text.From(Date.Month([Date])), 2, "0")  ),

    // Add Fiscal Period values for FiscalYear, FiscalQuarter, FiscalMonth
    FiscalYearAdded = Table.AddColumn(YYYYMM, "FiscalYear", each fnGetFiscalYear([Date])),
    FiscalQuarterYearAdded = Table.AddColumn(FiscalYearAdded, "FiscalQuarterYear", each fnGetFiscalQuarterYear([Date], month)),
    FiscalMonthYearAdded = Table.AddColumn(FiscalQuarterYearAdded, "FiscalMonthYear", each fnGetFiscalMonthYear([Date], month)),

    //Since Power Query doesn't have functions to return day or month names, use the lists created earlier for this
    MonthName = Table.AddColumn(FiscalMonthYearAdded, "MonthName", each MonthList{[MonthNumberOfYear]-1}),
    DayName = Table.AddColumn(MonthName, "DayName", each DayList{[DayOfWeekNumber]-1}),

    //use the System Date to determine Today
    IsToday = Table.AddColumn(DayName, "IsToday", each Date.IsInCurrentDay([Date])),
    WeekEnding = Table.AddColumn(IsToday, "Week Ending", each Date.EndOfWeek([Date])),
    WeekStarting = Table.AddColumn(WeekEnding, "Week Starting", each Date.StartOfWeek([Date])),

    //Group Dates into bands of Last7Days, Last30Days, Last90Days, Last180Days, Last360Days
    TodayFunction = DateTime.FixedLocalNow,
    Today = Table.AddColumn(WeekEnding, "Today", each TodayFunction()),

    CurrentFiscalYearFunction = fnGetFiscalYear,
    CurrentFiscalYearColumn = Table.AddColumn(Today, "CurrentFiscalYear", each CurrentFiscalYearFunction(CurrentDate)),

    CurrentFiscalMonthYearFunction = fnGetFiscalMonthYear,
    CurrentFiscalMonthYearColumn = Table.AddColumn(CurrentFiscalYearColumn, "CurrentFiscalMonthYear", each CurrentFiscalMonthYearFunction(CurrentDate,month)),

    CurrentFiscalQuarterYearFunction = fnGetFiscalQuarterYear,
    CurrentFiscalQuarterYearColumn = Table.AddColumn(CurrentFiscalMonthYearColumn , "CurrentFiscalQuarterYear", each CurrentFiscalQuarterYearFunction(CurrentDate,month)),

    WeekNumberOfYear= Table.AddColumn(CurrentFiscalQuarterYearColumn, "WeekNumberOfYear", each Text.From(Date.WeekOfYear([Date]))&"-"&Text.From([Year])),

    #"Changed Type3" = Table.TransformColumnTypes(WeekNumberOfYear,{{"Today", Date.Type}, {"DayOfMonth", Int64.Type}, {"MonthNumberOfYear", Int64.Type}, {"Year", Int64.Type}, {"DayOfWeekNumber", Int64.Type}, {"FiscalYear", Int64.Type}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type3", "Date", "DateValue"),

    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column1", {{"DateValue", Int64.Type}, {"Today", Int64.Type}}),

    #"Added L4LJanMonthAtual" = Table.AddColumn(#"Changed Type2", "L4LJanMonthAtual", each if [DateValue] <= [CurrentDate] then if [DateValue] >= [CurrentDate]-6 then 1 else 0 else 0),
    #"Added L4LJanMonthOld" = Table.AddColumn(#"Added L4LJanMonthAtual", "L4LJanMonthOld", each if [DateValue] <= [CurrentDate] then if [DateValue] >= [CurrentDate]-6 then 1 else 0 else 0),
    #"Added L4LJanYearAtual" = Table.AddColumn(#"Added L4LJanMonthOld", "L4LJanYearAtual", each if [DateValue] <= [CurrentDate] then if [DateValue] >= [CurrentDate]-6 then 1 else 0 else 0),
    #"Added L4LJanYearOld" = Table.AddColumn(#"Added L4LJanYearAtual", "L4LJanYearOld", each if [DateValue] <= [CurrentDate] then if [DateValue] >= [CurrentDate]-6 then 1 else 0 else 0),

    #"Added IsLast7Days" = Table.AddColumn(#"Changed Type2", "IsLast7Days", each if [DateValue] <= [Today] then if [DateValue] >= [Today]-6 then 1 else 0 else 0),
    #"Added IsLast30Days" = Table.AddColumn(#"Added IsLast7Days", "IsLast30Days", each if [DateValue] <= [Today] then if [DateValue] >= [Today]-29 then 1 else 0 else 0),
    #"Added IsLast90Days" = Table.AddColumn(#"Added IsLast30Days", "IsLast90Days", each if [DateValue] <= [Today] then if [DateValue] >= [Today]-89 then 1 else 0 else 0),
    #"Added IsLast180Days" = Table.AddColumn(#"Added IsLast90Days", "IsLast180Days", each if [DateValue] <= [Today] then if [DateValue] >= [Today]-179 then 1 else 0 else 0),
    #"Added IsLast365Days" = Table.AddColumn(#"Added IsLast180Days", "IsLast365Days", each if [DateValue] <= [Today] then if [DateValue] >= [Today]-364 then 1 else 0 else 0),
    #"Added IsNext90Days" = Table.AddColumn(#"Added IsLast365Days", "IsNext90Days", each if [DateValue] >= [Today] then if [DateValue] <= [Today]+90 then 1 else 0 else 0),
    #"Added IsCurrentFiscalQuarter" = Table.AddColumn(#"Added IsNext90Days", "IsCurrentFiscalQuarter", each if [FiscalQuarterYear] = [CurrentFiscalQuarterYear] then 1 else 0),
    #"Added IsCurrentFiscalMonth" = Table.AddColumn(#"Added IsCurrentFiscalQuarter", "IsCurrentFiscalMonth", each if [FiscalMonthYear] = [CurrentFiscalMonthYear] then 1 else 0),
    #"Added IsCurrentFiscalYear" = Table.AddColumn(#"Added IsCurrentFiscalMonth", "IsCurrentFiscalYear", each if [FiscalYear] = [CurrentFiscalYear] then 1 else 0),
    #"Added IsCurrentWeek" = Table.AddColumn(#"Added IsCurrentFiscalYear", "IsCurrentWeek", each if Date.IsInCurrentWeek([Date]) then 1 else 0),
    #"Added IsCurrentMonth" = Table.AddColumn(#"Added IsCurrentWeek", "IsCurrentMonth", each if Date.IsInCurrentMonth([Date]) then 1 else 0),
    #"Changed BinaryToLogical" = Table.TransformColumnTypes(#"Added IsCurrentMonth",{
        {"IsNext90Days", type logical}, {"IsLast7Days", type logical}, {"IsLast30Days", type logical},
        {"IsLast90Days", type logical}, {"IsLast180Days", type logical}, {"IsLast365Days", type logical},
        {"IsCurrentFiscalQuarter", type logical},{"IsCurrentFiscalYear", type logical}, {"IsCurrentFiscalMonth", type logical}, {"IsCurrentWeek", type logical}, {"IsCurrentMonth", type logical}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed BinaryToLogical",{"Today", "DateValue","CurrentFiscalYear","CurrentFiscalMonthYear","CurrentFiscalQuarterYear"})
in
    #"Removed Columns"
in
    Source
========================================
DIM_Calendar
//dCalendarioN:
let
    Source = fnDateTable(dCalend_FiscalYearEndMonthValue),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week Ending", type date}}),
    #"Added Month-Year" = Table.AddColumn(#"Changed Type", "Month-Year", each Text.Start([MonthName],3)&"-"&Text.From([Year])),
    #"Added MonthYearSortBy" = Table.AddColumn(#"Added Month-Year", "MonthYearSortBy", each [Year]*100+[MonthNumberOfYear]),
    #"Lang - Renamed Columns" = Table.RenameColumns(#"Added MonthYearSortBy",{
    {"DayOfMonth","Day Of Month"},
    {"MonthNumberOfYear","Month Number Of Year"},
    {"DayOfWeekNumber","Day Number Of Week"},
    {"MonthName","Month Name"},
    {"DayName","Day Name"},
    {"IsToday","Is Today"},
    {"WeekNumberOfYear","Week Number Of Year"},
    {"IsLast7Days","Is Last 7 Days"},
    {"IsLast30Days","Is Last 30 Days"},
    {"IsLast90Days","Is Last 90 Days"},
    {"IsLast180Days","Is Last 180 Days"},
    {"IsLast365Days","Is Last 365 Days"},
    {"IsNext90Days","Is Next 90 Days"},
    {"Month-Year","Month Year"},
    {"MonthYearSortBy","Month Year (Number)"},
    {"QuarterOfYear","Quarter Of Year"},
    {"FiscalYear","Fiscal Year"},
    {"IsCurrentFiscalMonth","Is Current Fiscal Month"},
    {"IsCurrentFiscalQuarter","Is Current Fiscal Quarter"},
    {"IsCurrentFiscalYear", "Is Current Fiscal Year"},
    {"FiscalQuarterYear","Fiscal Quarter Year"},
    {"FiscalMonthYear","Fiscal Month Year"},
    {"IsCurrentWeek","Is Current Week"},
    {"IsCurrentMonth","Is Current Month"}
    }),
    #"Sorted Rows" = Table.Sort(#"Lang - Renamed Columns",{{"Date", Order.Descending}})
in
    #"Sorted Rows"

========================================
dCalendario_LastRefresh
let
    Source = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})
in
    Source