Forum Discussion

mvcsharepointde's avatar
1 year ago

Defining a default order for our reprot

I have a PowerBI reprot inside power bi desktop app >> the poer bi reads data from external source. now i want to define a default sort for the users when the report loads. here is the setting i have:-

 

1) This is the formula i am using to get the data from the external API:-

 

 

let
    // Function to fetch data with pagination
    FetchTimeLedgerEntries = (Offset as number, Limit as number) as table =>
        let
            body = Text.Combine({"sAPIKey=", ApiKey}),
            SourceAPILogin = Json.Document(
                                Web.Contents(
                                    SmartAPI,
                                    [
                                        RelativePath="apilogin",
                                        Headers=[Accept="application/json", #"Content-Type"="application/x-www-form-urlencoded",#"API-Key"=Token],
                                        Content=Text.ToBinary(body)
                                    ]
                                )
                            ),
            tblGetUID = Table.FromList(SourceAPILogin, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            expGetUID = Table.ExpandRecordColumn(tblGetUID, "Column1", {"UID", "LanguageCode", "DatabaseVersion"}, {"Column1.UID", "Column1.LanguageCode", "Column1.DatabaseVersion"}),
            GetUID = expGetUID{0}[Column1.UID],
            Source = Json.Document(
                        Web.Contents(
                            SmartAPI,
                            [
                                RelativePath = "timeledgerentry",
                                Query = [#"offset"=Text.From(Offset), #"limit"=Text.From(Limit)], // Adding Offset and Limit parameters
                                Headers=[Accept="application/json", #"Content-Type"="application/json",#"API-Key"=Token, UID=GetUID, DeviceID=ApiKey] 
                            ]
                        )
                    ),
            tblAll = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            tableHasRecords = Table.RowCount(tblAll) > 0,
            Result = if tableHasRecords then
                        Table.ExpandRecordColumn(tblAll,"Column1", Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(tblAll,"Column1"), each _ <> "" and _ <> null))),Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(tblAll,"Column1"), each _ <> "" and _ <> null))))
                     else
                        #table(Table.ColumnNames(tblAll), {})
        in
            Result,

    // Loop through pages to get all records
    Offset = 0,
    Limit = 10000,
    PageSize = 10000,
    AllPages = List.Generate(
                    () => [Result = FetchTimeLedgerEntries(Offset, Limit), Offset = Offset + PageSize],
                    each Table.RowCount([Result]) > 0,
                    each [Result = FetchTimeLedgerEntries([Offset], Limit), Offset = [Offset] + PageSize],
                    each [Result]
                ),
    tblTimeLedgerEntries = Table.Combine(AllPages),
    #"Changed Type" = Table.TransformColumnTypes(tblTimeLedgerEntries,{{"Date", type datetime}, {"StartTime", type datetime}, {"EndTime", type datetime}, {"LogStart", type datetime}, {"LogEnd", type datetime}})
in
    #"Changed Type"

 

 

2) I defined the following inside the sort:-

 

 

now what i did is that i sort the data using the Date field >> and i publish the report to the workspace>> where when i opened the report using the web browser >> the reprot will be sorted using the Date.. so is doing the sort on the desktop app and publish the reprot the correct way to define the default sort ??

 

 

3 Replies

  • mvcsharepointde sorry your question is not clear, could you please provide a bit more context on what you are trying to achieve?

  • parry2ki want to sort the reprot by Date by default,  when the users first visit the report. currently what i did is that i filtered the report based on the Date field inside the power bi desktop >> then i publish the report.  when i visited the reprot inside the browser, i can see that the report is sorted by the Date correctly by default... so is what i did the valid appraoch to define a default sort for the report? or i achive it by chnace ?