Technology, Analytics, DATA & Innovation

Wednesday, February 2, 2022

Power Query - dCalendar with ISOWeek

fnc_ISOWeek:

based on <https://en.wikipedia.org/wiki/ISO_week_date#Calculating_the_week_number_of_a_given_date>

M / Power Query doesn't have a native ISO8601 Week Number function, and DAX's

`weeknum(x, 21)` doesn't give the correct ISO Week-Year.

homepage: <https://gist.github.com/r-k-b/18d898e5eed786c9240e3804b167a5ca>

let
getISO8601Week = (someDate as date) =>
let
getDayOfWeek = (d as date) =>
let
result = 1 + Date.DayOfWeek(d, Day.Monday)
in
result,
getNaiveWeek = (inDate as date) =>
let
// monday = 1, sunday = 7
weekday = getDayOfWeek(inDate),
weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)),
ordinal = Date.DayOfYear(inDate),
naiveWeek = Number.RoundDown(
(ordinal - weekday + 10) / 7
)
in
naiveWeek,
thisYear = Date.Year(someDate),
priorYear = thisYear - 1,
nwn = getNaiveWeek(someDate),
lastWeekOfPriorYear =
getNaiveWeek(#date(priorYear, 12, 28)),
// http://stackoverflow.com/a/34092382/2014893
lastWeekOfThisYear =
getNaiveWeek(#date(thisYear, 12, 28)),
weekYear =
if
nwn < 1
then
priorYear
else
if
nwn > lastWeekOfThisYear
then
thisYear + 1
else
thisYear,
weekNumber =
if
nwn < 1
then
lastWeekOfPriorYear
else
if
nwn > lastWeekOfThisYear
then
1
else
nwn,
week_dateString =
Text.PadStart(
Text.From(
Number.RoundDown(weekNumber)
),
2,
"0"
)
in
Text.From(weekYear) & "-W" & week_dateString & "-" & Text.From(getDayOfWeek(someDate))
in
getISO8601Week

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

dCalendar:

let

Source = List.Dates,

// Step01 = Source(#date(2019, 1, 1), Duration.Days(DateTime.Date(Date.AddDays(DateTime.FixedLocalNow(),1)) - #date(2019,1,1)), #duration(1, 0, 0, 0)),

Step01 = Source(#date(2016, 1, 1), Duration.Days(DateTime.Date(Date.AddDays(DateTime.FixedLocalNow(),1)) - #date(2016,1,1)), #duration(1, 0, 0, 0)),

Step02 = Table.FromList(Step01, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

Step03 = Table.AddIndexColumn(Step02, "Index", 1, 1),

Step04 = Table.RenameColumns(Step03,{{"Column1", "Date"}}),

Step05 = Table.AddColumn(Step04, "Year", each Date.Year([Date])),

Step06 = Table.AddColumn(Step05, "Month Number", each Number.ToText(Date.Month([Date]),"00")),

Step07 = Table.AddColumn(Step06, "Day", each Number.ToText(Date.Day([Date]),"00")),

Step08 = Table.AddColumn(Step07, "Day Name", each Date.ToText([Date],"ddd")),

Step09 = Table.AddColumn(Step08, "Month Name", each Date.ToText([Date],"MMM")),

Step10 = Table.AddColumn(Step09, "Quarter Number", each Date.QuarterOfYear([Date])),

Step11 = Table.AddColumn(Step10, "Short Year", each Date.Year([Date])),

Step12 = Table.TransformColumnTypes(Step11,{{"Short Year", type text}}),

Step13 = Table.SplitColumn(Step12,"Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),

Step14 = Table.TransformColumnTypes(Step13,{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),

Step15 = Table.RemoveColumns(Step14,{"Short Year.1"}),

Step16 = Table.RenameColumns(Step15,{{"Short Year.2", "Short Year"}}),

Step17 = Table.AddColumn(Step16, "Quarter Year", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"00")),

Step18 = Table.AddColumn(Step17, "StartWeek", each Date.StartOfWeek([Date])),

Step19 = Table.AddColumn(Step18, "YYYYMM", each Number.ToText(Date.Year([Date])) & Number.ToText(Date.Month([Date]),"00")),

Step20n = Table.AddColumn(Step19, "YYYYWeekYear", each Number.ToText(Date.Year([Date])) & Number.ToText(Date.WeekOfYear([Date], Day.Saturday),"00")),

Step20 = Table.AddColumn(Step20n, "WeekDay", each Number.ToText(Date.DayOfWeek([Date], Day.Saturday),"00")),

Step21 = Table.AddColumn(Step20, "YYYYMMDD", each Number.ToText(Date.Year([Date])) & Number.ToText(Date.Month([Date]),"00") & Number.ToText(Date.Day([Date]),"00")),

Step22 = Table.AddColumn(Step21, "MMDD", each Number.ToText(Date.Month([Date]),"00") & Number.ToText(Date.Day([Date]),"00")),

Step23 = Table.TransformColumnTypes(Step22,{{"Date", type date}, {"Index", Int64.Type}, {"Year", Int64.Type}, {"Month Number", Int64.Type}, {"Day", Int64.Type}, {"Day Name", type text}, {"Month Name", type text}, {"Quarter Number", Int64.Type}, {"Short Year", Int64.Type}, {"Quarter Year", type text}, {"StartWeek", type date}, {"YYYYMM", Int64.Type}, {"WeekDay", Int64.Type}, {"YYYYMMDD", Int64.Type}}),

Step24 = Table.SelectRows(Step23, each true),

Step25 = Table.Sort(Step24,{{"YYYYMMDD", Order.Descending}}),

Step26 = Table.AddColumn(Step25, "WeekName", each Number.ToText([WeekDay]) & " - " & [Day Name]),

Step27 = Table.AddColumn(Step26, "QtdDias", each DateTime.Date(Date.AddDays(DateTime.FixedLocalNow(),1)) - [Date]),

Step28 = Table.AddColumn(Step27, "WeekYear", each Date.WeekOfYear([Date])),

Step29 = Table.TransformColumnTypes(Step28,{{"QtdDias", Int64.Type}, {"Date", type date}, {"Index", Int64.Type}, {"Year", Int64.Type}, {"Month Number", Int64.Type}, {"Day", Int64.Type}, {"Day Name", type text}, {"Month Name", type text}, {"Quarter Number", Int64.Type}, {"Short Year", Int64.Type}, {"Quarter Year", type text}, {"StartWeek", type date}, {"YYYYMM", Int64.Type}, {"WeekDay", Int64.Type}, {"YYYYMMDD", Int64.Type}, {"MMDD", Int64.Type}, {"WeekName", type text}, {"WeekYear", Int64.Type}}),

Step30 = Table.AddColumn(Step29, "Zodiac", each if ([MMDD] >= 0121 and [MMDD] <= 0218) then "Z01" else

if ([MMDD] >= 0219 and [MMDD] <= 0320) then "Z02" else

if ([MMDD] >= 0321 and [MMDD] <= 0420) then "Z03" else

if ([MMDD] >= 0421 and [MMDD] <= 0521) then "Z04" else

if ([MMDD] >= 0522 and [MMDD] <= 0621) then "Z05" else

if ([MMDD] >= 0622 and [MMDD] <= 0722) then "Z06" else

if ([MMDD] >= 0723 and [MMDD] <= 0823) then "Z07" else

if ([MMDD] >= 0824 and [MMDD] <= 0923) then "Z08" else

if ([MMDD] >= 0924 and [MMDD] <= 1023) then "Z09" else

if ([MMDD] >= 1024 and [MMDD] <= 1122) then "Z10" else

if ([MMDD] >= 1123 and [MMDD] <= 1221) then "Z11" else

if ([MMDD] >= 1222 or [MMDD] <= 0120) then "Z12" else "???"),

#"Invoked Custom Function" = Table.AddColumn(Step30, "ISOWeek", each fnc_ISOWeek([Date]))

#"Invoked Custom Function"

Friday, January 7, 2022

DAX - SELECT with GROUP BY = EVALUATE SELECTCOLUMNS ( SUMMARIZECOLUMNS))

EVALUATE

SELECTCOLUMNS (
SUMMARIZECOLUMNS(
    SAP_FAT_Vendas_py[PLANT],
    SAP_FAT_Vendas_py[CALDAY],
    SAP_FAT_Vendas_py[/BIC/ZCARDNUM1],
    SAP_FAT_Vendas_py[CUST_SALES],
    SAP_FAT_Vendas_py[/BIC/ZMATERIAL],
    "Vol3", [.Vol3],
    "QtdRegs", [.QtdRegs]
),
    SAP_FAT_Vendas_py[PLANT],
    SAP_FAT_Vendas_py[CALDAY],
    "CARDNUM1",SAP_FAT_Vendas_py[/BIC/ZCARDNUM1],
    SAP_FAT_Vendas_py[CUST_SALES],
    "MATERIAL", SAP_FAT_Vendas_py[/BIC/ZMATERIAL],
    "Vol3", [Vol3],
    "QtdRegs", [QtdRegs]
)

Tuesday, November 23, 2021

Python - Script Debug DBMS

# %%
import datetime
from dateutil.relativedelta import relativedelta
#------------------------------------------------------------------------------------------------
#BEGIN Time
t_aFull=""
t_bFull=""
def diff(t_bFull, t_aFull):
    t_diffFull = relativedelta(t_bFull,t_aFull)  # later/end time comes first!
    return '{h}h {m}m {s}s'.format(h=t_diffFull.hours, m=t_diffFull.minutes, s=t_diffFull.seconds)

t_aFull = datetime.datetime.now()
print("========================== ")
print("Time Starting: ",t_aFull)
print("==========================") 
#------------------------------------------------------------------------------------------------

########################################Script Body##############################################

# %%
#------------------------------------------------------------------------------------------------
#Script de Tempo End
t_bFull = datetime.datetime.now()
#diff(t_bFull,t_aFull)
print("========================== ")
print("Time Finish: ",t_bFull)
print("==========================") 
print("Time Total: ",diff(t_aFull,t_bFull))
print("==========================") 
#------------------------------------------------------------------------------------------------

Thursday, November 11, 2021

SQL - scripts DBA

/*query DBAs */
--=================================================================================
/*Consulta Tamanho das Tabelas*/
SELECT
CASE  
when t.NAME = '/BIC/FZSD_C23'  then 'WS - Vendas Integradas Histórico'
when t.NAME = '/BIC/VZSD_C23F' then 'FACTVIEW - INFOCUBE ZSD_C23     '
ELSE '???' END as ETL,  
    t.NAME AS Entidade,
	COUNT(DISTINCT(p.partition_id)) AS partition_id,
    SUM(DISTINCT(p.rows)) AS Registros,  
	COUNT(DISTINCT(c.column_id)) AS Qtd_Cols,
    SUM(a.total_pages) * 8 AS EspacoTotalKB,
    SUM(a.used_pages) * 8 AS EspacoUsadoKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacoNaoUsadoKB,
	MAX(t.modify_date) AS Ultima_Atualizacao,
	MAX(t.create_date) AS Ultima_Atualizacao2
FROM
    sys.tables t
INNER JOIN
    sys.all_columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE upper(t.NAME) LIKE upper( '%logy%' )
    --t.NAME NOT LIKE 'xpto%' and
    --t.is_ms_shipped = 0 and
    --i.OBJECT_ID > 255 
GROUP BY
    t.Name --	,p.partition_id
ORDER BY  Ultima_Atualizacao DESC ,
 4 desc
 
 --Registros DESC ;

--=================================================================================
/*Consulta Colunas das Tabelas like*/
SELECT distinct
    t.NAME AS Entidade,
	c.Name AS Coluna , c.max_length, c.precision, c.object_id
FROM
    sys.tables t
INNER JOIN
    --select * from 
	sys.all_columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE upper(c.NAME) LIKE upper( '%logy%' )
    --t.NAME NOT LIKE 'xpto%' and
    --t.is_ms_shipped = 0 and
    --i.OBJECT_ID > 255 
order by 1,2

--=================================================================================
/*Consulta Global Variables
select @@VERSION                      ;
select @@SERVERNAME                   ;

select @@CONNECTIONS                  ;
select @@MAX_CONNECTIONS              ;
select @@CPU_BUSY                     ;
select @@ERROR                        ;
select @@IDENTITY                     ;
select @@IDLE                         ;
select @@IO_BUSY                      ;
select @@LANGID                       ;
select @@LANGUAGE                     ;
--select @@MAXCHARLEN                   ;
select @@PACK_RECEIVED                ;
select @@PACK_SENT                    ;
select @@PACKET_ERRORS                ;
select @@ROWCOUNT                     ;
select @@SPID                         ;
select @@TEXTSIZE                     ;
select @@TIMETICKS                    ;
select @@TOTAL_ERRORS                 ;
select @@TOTAL_READ / @@TOTAL_WRITE   ;
select @@TRANCOUNT                    ;
*/

Wednesday, September 15, 2021

Power BI Service - Power Query - DataFormat.Error: Invalid cell value '#N/A'

There are two points to be evaluated:

1. If you want to discard the errors

In this case, just run:

#"Removed errors" = Table.RemoveRowsWithErrors(StepBefore),

2. Whether to adjust errors due to some columns having value for analysis.

You should select the errors to punctually analyze the non-wrong values and then run the following scripts:

#"Kept errors" = Table.SelectRowsWithErrors(StepBefore),

#"Replaced errors" = Table.ReplaceErrorValues(#"Kept errors", {{[ColumnWithError], null}}),

After detecting the column in error, you can delete the Step:

#"Kept errors" = Table.SelectRowsWithErrors(StepBefore),

I hope help us!!!

Wednesday, July 21, 2021

Power BI with Python 3.7 - Step by Step

#Commands

conda --version

python --version

conda info --envs

conda info

conda list

conda env list

conda list -n myenv scipy

conda search jupyter

conda list

conda search tensorflow

#Install/Uninstall Commands

conda install --help (...)

conda remove -n PyPWBI --all -y

#IMPORT Environment

conda create --name PyPWBI python=3.7

conda deactivate

conda activate PyPWBI

source activate PyPWBI (validar...)

conda install numpy pandas matplotlib seaborn scikit-learn jupyter nose scipy

conda install -c conda-forge squarify

conda install -c conda-forge pandas-profiling

conda install -c conda-forge schedule

pip install speedtest-cli

pip install openpyxl

pip install investpy

pip uninstall matplotlib pillow numpy

pip install matplotlib pillow numpy

pip install yfinance --upgrade --no-cache-dir

Monday, July 5, 2021

Power Query - Connect API REST

1. Example (in parts)

GetAccessToken:
() =>
let
url = "https://wwwXPTO.com/Auth/GetApiToken",
body = "{ ""user"": ""userXPTO"", ""pwd"": ""123$""} ",
AccessToken = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/json"], Content=Text.ToBinary(body)])),
access_token = AccessToken[token]
in
access_token
========================================================
GetList:
let
// Get_Data
Query = () => let
Source = Json.Document(Web.Contents("https://wwwXPTO.com/GetList", [Headers=[Authorization="Bearer " & GetAccessToken()]])),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{4}[Value]
in
Value
in
Query
========================================================
FAT_Table:
let
Source = GetList(),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

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

2. Example Full

let
tenantId = "435435435345435435",
clientId = "34543543534535345543",
clientSecret = "35435435435435435435",
grantType = "client_credentials",
resource = "https://graph.windows.net/",
endpointUsers = "/users",
apiVersion = "?api-version=1.6",
baseURL="https://login.microsoftonline.com",
relativePath = "/"&tenantId&"/oauth2/token",
url = baseURL & relativePath,
body2 = [
client_id=clientId,
grant_type=grantType,
client_secret=clientSecret,
resource=resource],
Source = Json.Document(Web.Contents(url, [Headers=[#"Accept" = "application/json", #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],Content = Text.ToBinary(Uri.BuildQueryString(body2))])),
accessToken = Source[access_token],
tokenType = Source[token_type],
usersURL = tenantId & endpointUsers &apiVersion,
fullAccessToken = tokenType&" "&accessToken,
Users = Json.Document(Web.Contents(resource & tenantId & endpointUsers &apiVersion,[Headers=[ContentType="application/json", Authorization=fullAccessToken, RelativePath=usersURL]])),
value = Users[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"odata.type", "objectType", "objectId", "deletionTimestamp", "accountEnabled", "ageGroup", "assignedLicenses", "assignedPlans", "city", "companyName", "consentProvidedForMinor", "country", "createdDateTime", "creationType", "department", "dirSyncEnabled", "displayName", "employeeId", "facsimileTelephoneNumber", "givenName", "immutableId", "isCompromised", "jobTitle", "lastDirSyncTime", "legalAgeGroupClassification", "mail", "mailNickname", "mobile", "onPremisesDistinguishedName", "onPremisesSecurityIdentifier", "otherMails", "passwordPolicies", "passwordProfile", "physicalDeliveryOfficeName", "postalCode", "preferredLanguage", "provisionedPlans", "provisioningErrors", "proxyAddresses", "refreshTokensValidFromDateTime", "showInAddressList", "signInNames", "sipProxyAddress", "state", "streetAddress", "surname", "telephoneNumber", "thumbnailPhoto@odata.mediaEditLink", "usageLocation", "userIdentities", "userPrincipalName", "userState", "userStateChangedOn", "userType"}, {"odata.type", "objectType", "objectId", "deletionTimestamp", "accountEnabled", "ageGroup", "assignedLicenses", "assignedPlans", "city", "companyName", "consentProvidedForMinor", "country", "createdDateTime", "creationType", "department", "dirSyncEnabled", "displayName", "employeeId", "facsimileTelephoneNumber", "givenName", "immutableId", "isCompromised", "jobTitle", "lastDirSyncTime", "legalAgeGroupClassification", "mail", "mailNickname", "mobile", "onPremisesDistinguishedName", "onPremisesSecurityIdentifier", "otherMails", "passwordPolicies", "passwordProfile", "physicalDeliveryOfficeName", "postalCode", "preferredLanguage", "provisionedPlans", "provisioningErrors", "proxyAddresses", "refreshTokensValidFromDateTime", "showInAddressList", "signInNames", "sipProxyAddress", "state", "streetAddress", "surname", "telephoneNumber", "thumbnailPhoto@odata.mediaEditLink", "usageLocation", "userIdentities", "userPrincipalName", "userState", "userStateChangedOn", "userType"})
in
#"Expanded Column1"

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

3. example with Body parameters

let

url = "https://j?????????????????",

body = "{ ""hCreationDate"": { ""$gte"": ""2021-08-01T00:00:00.000Z"",""$lte"": ""2021-08-03T00:00:00.000Z"" } } ",

vData = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/json" , Authorization="Bearer " & GetAcessToken()], Content=Text.ToBinary(body)])),

results = vData[results],

#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"_id", "action", "feedId", "segment", "userId", "hCreationDate"}, {"_id", "action", "feedId", "segment", "userId", "hCreationDate"})

#"Expanded Column1"