Technology, Analytics, DATA & Innovation

Tuesday, May 26, 2020

Power Query - Remove letters from a string

Line = Table.AddColumn(PrevLine, "NewVar", 
each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([OldVar]),
each if Value.Is(Value.FromText(_), type number) then _ else null))))

Thursday, February 27, 2020

Power BI - Calculate Measure When Blank Value

I created 3 ways to calculate these measures

//First calculate the SUM
.ValorAPOSum = SUM('TABLE'[Valor])

//After SUM, First way
.ValorAPO1_Sum = DIVIDE(CALCULATE([.ValorAPOSum], DATESINPERIOD(Calendario[Date], LASTDATE(Calendario[Date])-1, -7, DAY)), 7,0)
.ValorAPO1 = if(ISBLANK([.ValorAPOSum]),[.ValorAPO1_Sum],[.ValorAPOSum])
.ValorAPO1_All = SUMX(Calendario,[.ValorAPO1])

//After SUM, Second way
.ValorAPOSumMoving = CALCULATE([.ValorAPOSum],DATESINPERIOD(Calendario[Date],LASTDATE(Calendario[Date]),-7,DAY))
.ValorAPO2_Sum = [.ValorAPOSumMoving]/CALCULATE(DISTINCTCOUNT(Calendario[Date]),DATESINPERIOD(Calendario[Date],LASTDATE(Calendario[Date]),-7,DAY))
.ValorAPO2 = if(ISBLANK([.ValorAPOSum]),[.ValorAPO2_Sum],[.ValorAPOSum])
.ValorAPO2_All = SUMX(Calendario,[.ValorAPO2])

//After SUM, Third way
.ValorAPO3_AVG =
VAR LAST_DATE = LASTDATE(Calendario[Date])
Var Sum_7 = calculate([.ValorAPOSum],DATESBETWEEN(Calendario[Date],DATEADD(LAST_DATE, -6, DAY),LAST_DATE))
Var Sum_all_7 = calculate([Valor APO],DATESBETWEEN(Calendario[Date],DATEADD(LAST_DATE, -6, DAY),LAST_DATE))
//return divide(Sum_7 ,Sum_all_7)
return divide(Sum_7,7)
.ValorAPO3 = if(ISBLANK([.ValorAPOSum]),[.ValorAPO3_AVG],[.ValorAPOSum])
.ValorAPO3_All = SUMX(Calendario,[.ValorAPO3])

//Count Table relations
.QtdCountry2 = COUNTROWS(

    FILTER(DIM_Continente_Locations,

    CONTAINS(FAT_Corona3,FAT_Corona3[Country_Other],DIM_Continente_Locations[Country_Name.1])))

Thursday, February 6, 2020

Power BI - Creating Tax measure between two tables

Table_Moviments:

Table_Tax

____________________________________________________________________

.00YYYYMMDD = MAX(Table_Moviments[a_YYYYMMDD])

____________________________________________________________________

.01GetTax =

Var v_Val01 = "Variavel01"

return

CALCULATE(SUM(Table_Tax[ValorHora]),

FILTER(Table_Tax,

Table_Tax[Referencia] = v_Val01 &&

Table_Tax[DataInicial] <= [.00YYYYMMDD]&&

Table_Tax[DataFinal] >= [.00YYYYMMDD]

)

____________________________________________________________________

.02GetTaxGroup = SUMX(

SUMMARIZE(

Table_Moviments,

Table_Moviments[a_TicketPrio],

Table_Moviments[a_TicketDate]),

[.01GetTax]

)

____________________________________________________________________

.03QtdHoursRealGroup = SUMX(

SUMMARIZE(

FATTickets_WorkLogs,

FATTickets_WorkLogs[a_TicketPrio],

FATTickets_WorkLogs[a_TicketDate]),

[HoursMov])

____________________________________________________________________

.04VlrCostRealGroup = SUMX(

SUMMARIZE(

Table_Moviments,

Table_Moviments[a_TicketPrio],

Table_Moviments[a_TicketDate]),

([.03QtdHoursRealGroup]*[.02GetTaxGroup])

)

___________________________________________________________________

Thursday, January 16, 2020

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

Wednesday, January 15, 2020

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