Forum Discussion
Present and past data breach
Can anyone help me? I want the data for the last 16 weeks to be shown, but it keeps giving me errors in the codes.
I think the problem is here somewhere:
Final = Recursion(-15, Table(-16)), RecursiveJoinAbstract(0,8,RecursiveJoinAbstract(0,4,RecursiveJoin(0),RecursiveJoin(4)),RecursiveJoinAbstract(8,12,RecursiveJoin(8),RecursiveJoin(12))),
Benutzerdefiniert1 = Table.RenameColumns(Final, {{Table.ColumnNames(Final){0}, "ID"}}),
#"Entpivotierte Spalten" = Table.UnpivotOtherColumns(Benutzerdefiniert1, {"ID"}, "Attribut", "Wert"),
#"Umbenannte Spalten" = Table.RenameColumns(#"Entpivotierte Spalten",{{"Attribut", "KW"}}),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"KW", type number}}),
let
// Concatenates the Client ID & Client Secret and converts to base64
authKey = "Basic " & Binary.ToText(Text.ToBinary("CRJE-SP-REPORTINGV2" & ":" & "6icCt58FDLAi0IzLFKzVnBUYth1uHm"),BinaryEncoding.Base64),
url = "https://identity.apaleo.com/connect/token",
// Uses the apaleo POST token method to obtain a bearer token
GetJson = Web.Contents(url,
[
Headers = [#"Authorization"=authKey,
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("grant_type=client_credentials")
]
),
FormatAsJson = Json.Document(GetJson),
// Gets token from the Json response
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessToken,
// Uses the api
TodayDate = DateTime.Date(DateTime.LocalNow()),
CalendarWeek = (x as number) => Date.WeekOfYear(Date.AddDays(TodayDate,x*7)),
StartDate = (x as number) => Date.ToText(Date.StartOfWeek(Date.AddDays(TodayDate,x*7), Day.Monday),"yyyy-MM-dd"),
EndDate = (x as number) => Date.ToText(Date.EndOfWeek(Date.AddDays(TodayDate,x*7), Day.Monday),"yyyy-MM-dd"),
Minus6Weeks = StartDate(-6),
GetJsonQuery = (x as number) => Web.Contents("https://api.apaleo.com/reports/v1/reports/property-performance?propertyId=CATRINA&from="&StartDate(x)&"&to="&EndDate(x),
[
Headers = [#"Authorization"=AccessTokenHeader]
]
),
ReplaceValue = (inputTable, row) =>
Table.ReplaceValue(inputTable,each [Value], each if Value.Is([Value], type record) = true then [Value][amount] else [Value],Replacer.ReplaceValue,{"Value"}),
TableName = (x as number) => "Name"&Number.ToText(CalendarWeek(x)),
TableValue = (x as number) => ""&Number.ToText(CalendarWeek(x)),
TableRename = (x as number, input as table) => Table.RenameColumns(input, {{"Value", TableValue(x)},{"Name", TableName(x)}}),
Table = (x as number) =>
let
tempTable = Record.ToTable(Json.Document(GetJsonQuery(x))),
tempTableFlattened = ReplaceValue(tempTable,15),
output = TableRename(x, tempTableFlattened)
in
output,
Recursion = ( x, inputTable ) =>
if x = 17
then inputTable
else
let
JoinTable = Table.Join(inputTable,{TableName(-16)},Table(x),{TableName(x)}, JoinKind.FullOuter),
JoinResult = Table.RemoveColumns(JoinTable, {TableName(x)}),
Result = @Recursion ( x + 1, JoinResult )
in
Result,
TableJoin = (x as number) =>
let
JoinTable = Table.Join(Table(x),{TableName(x)},Table(x+1),{TableName(x+1)}, JoinKind.FullOuter),
JoinResult = Table.RemoveColumns(JoinTable, {TableName(x+1)})
in JoinResult,
RecursiveJoin = (x) =>
let
JoinTable = Table.Join(TableJoin(x),{TableName(x)},TableJoin(x+2),{TableName(x+2)}, JoinKind.FullOuter),
JoinResult = Table.RemoveColumns(JoinTable, {TableName(x+2)})
in JoinResult,
RecursiveJoinAbstract = (a as number, b as number,y,z) =>
let
JoinTable = Table.Join(y,{TableName(a)},z,{TableName(b)}),
JoinResult = Table.RemoveColumns(JoinTable, {TableName(b)})
in JoinResult,
Final = Recursion(-15, Table(-16)), RecursiveJoinAbstract(0,8,RecursiveJoinAbstract(0,4,RecursiveJoin(0),RecursiveJoin(4)),RecursiveJoinAbstract(8,12,RecursiveJoin(8),RecursiveJoin(12))),
Benutzerdefiniert1 = Table.RenameColumns(Final, {{Table.ColumnNames(Final){0}, "ID"}}),
#"Entpivotierte Spalten" = Table.UnpivotOtherColumns(Benutzerdefiniert1, {"ID"}, "Attribut", "Wert"),
#"Umbenannte Spalten" = Table.RenameColumns(#"Entpivotierte Spalten",{{"Attribut", "KW"}}),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"KW", type number}}),
#"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ", "Benutzerdefiniert", each if [KW]*7 - Date.DayOfYear(TodayDate) < 0 then Date.Year(TodayDate)+1 else Date.Year(TodayDate)),
#"Umbenannte Spalten1" = Table.RenameColumns(#"Hinzugefügte benutzerdefinierte Spalte",{{"Benutzerdefiniert", "Year"}}),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Umbenannte Spalten1",{{"Wert", type number}})
in
#"Geänderter Typ1"
1 Reply
- v-alq-msftCommunity Support
Hi, CatrinaExp
Could you please show us the corresponding steps with error messages? You may share some screenshots with OneDrive for business. Do mask sensitive data before uploading. Thanks.
Best Regards
Allan