Forum Discussion

CatrinaExp's avatar
CatrinaExp
New Member
5 years ago

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-msft's avatar
    v-alq-msft
    Community 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