Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 ??
@mvcsharepointde yes, this is the way to achieve what you are looking for. Not sure if you have further question on it
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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 ?
@mvcsharepointde sorry your question is not clear, could you please provide a bit more context on what you are trying to achieve?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
108 | |
104 | |
95 | |
70 |
User | Count |
---|---|
173 | |
134 | |
132 | |
101 | |
95 |