Create custome sort in Power BI

In Power BI, you can achieve custom sorting by creating a separate column that defines the sort order for your "state" column. Here's a general guide:
Create a Sort Order Column:

• Create a new column in your table, let's call it "Sort Order."

• Assign a numerical value to each state based on your desired sorting order.

Sort by the New Column:

• In your visualizations or tables, sort by the newly created "Sort Order" column rather than the original "state" column.

This way, you can define a custom sort order for your states independent of alphabetical order.
For example, if you want to sort by population size, you might assign a number to each state based on its population.
If you provide more details about your sorting criteria, I can give you a more specific example.

If you want to sort your "state" column based on a custom order like "planned - New - Accepted - done - Resolved - Closed," you can follow these steps:

Create a Custom Sort Order Column:

Create a new column (let's call it "CustomSortOrder") and assign a numerical value to each state based on your desired order. For example:

Planned: 1

New: 2

Accepted: 3

Done: 4

Resolved: 5

Closed: 6

Sort by the Custom Sort Order Column:

In your Power BI visualizations or tables, sort by the "CustomSortOrder" column rather than the original "state" column.

This way, your states will be sorted according to the custom order you defined in the "CustomSortOrder" column.

Advantages of dataflows

 Using dataflows in Power BI can offer several advantages over a direct connection to a large data source, especially when dealing with performance issues. Here are some advantages of using dataflows:

Data Transformation and Preparation:

Dataflows allow you to perform ETL (Extract, Transform, Load) operations in a separate step from the report creation. This means you can transform and clean the data in a more structured and optimized way before it reaches the report, improving efficiency.

Data Compression:

Dataflows automatically apply data compression techniques, reducing the amount of data transferred and stored. This can lead to faster refresh times and improved overall performance.

Incremental Refresh:

With dataflows, you can implement incremental refresh strategies, where only the new or modified data is refreshed instead of refreshing the entire dataset. This is particularly useful for large datasets as it significantly reduces refresh times.

Parallel Processing:

Dataflows allow for parallel processing during data transformation and loading, distributing the workload across multiple nodes. This can lead to faster data processing times compared to a sequential processing approach.

Separation of Concerns:

By using dataflows, you separate the data preparation and transformation logic from the report creation. This enhances maintainability and makes it easier to troubleshoot and debug issues related to data transformation.

Reuse of Data Transformations:

Dataflows can be reused across multiple reports and dashboards. Once you define a set of transformations, you can use them in different contexts without duplicating effort. This promotes consistency in data preparation and reduces redundancy.

Data Storage in the Power BI Service:

Dataflows can store data in the Power BI service, reducing the need to repeatedly transfer large datasets over the network. This can result in faster report rendering times and a more responsive user experience.

Optimized Data Models:

Dataflows can create optimized data models, which can result in better query performance when users interact with the report. This is because dataflows can aggregate and summarize data in a way that is tailored to the reporting requirements.

Scheduled Refresh:

Dataflows can be scheduled to refresh at specific intervals, ensuring that the data is always up-to-date without requiring manual intervention. This is crucial for maintaining the accuracy of reports over time.

Integration with Power Query:

Dataflows leverage the Power Query engine for data transformation, providing a familiar and powerful tool for users already accustomed to working with Power Query in Power BI Desktop.

By leveraging these advantages, dataflows can significantly improve the efficiency and performance of your Power BI reports, especially when dealing with large and complex datasets.

P10

 Subject: Request for One Day Leave for Daughter-in-Law's Mundan Ceremony

Dear [Manager's Name],

I hope this email finds you well. I am writing to request a one-day leave on [Date] to attend my daughter-in-law's Mundan ceremony, which will be taking place at Tirupathi. This event holds significant cultural and religious importance for my family, and I would be honored to be present to support and participate in this special occasion.

I understand the importance of my responsibilities at work and assure you that I will ensure a smooth handover of my tasks to a colleague or provide any necessary guidance to ensure that there is no disruption during my absence.

I apologize for any inconvenience this leave may cause and appreciate your understanding and consideration of my request. I will make sure to complete any pending work before my leave and ensure that my absence does not impact the team's progress.

Thank you for your attention to this matter. I am looking forward to your favorable response.

Best regards,

[Your Name]

P9

 let

    // Load your calendar and iterations tables

    CalendarTable = ...,

    IterationsTable = ...,

    

    // Merge the tables without any key

    CombinedTable = Table.NestedJoin(CalendarTable, {}, IterationsTable, {}, "Merged", JoinKind.LeftOuter),

    

    // Define a custom function to check the condition

    CheckCondition = (row) => row[DateUTC] = row[StartDate],

    

    // Expand the merged table with new columns based on the condition

    ExpandedTable = Table.AddColumn(CombinedTable, "ExpandedColumns", each if CheckCondition(_) then [Column1, Column2, ...] else null),

    

    // Filter out rows where the condition is not met and remove unnecessary columns

    FilteredTable = Table.SelectRows(ExpandedTable, each [ExpandedColumns] <> null),

    FinalTable = Table.RemoveColumns(FilteredTable, {"ExpandedColumns"})

in

    FinalTable

P8

 let

    // Assuming "Calendar3" is the name of your table and "StartDate" is the name of your date column

    // Create a list of all unique dates in the "StartDate" column

    DatesList = List.Sort(List.Distinct(Calendar3[StartDate])),

    // Find the minimum and maximum dates from the list

    MinDate = List.Min(DatesList),

    MaxDate = List.Max(DatesList),

    // Generate a list of all dates between the minimum and maximum dates

    AllDates = List.Dates(MinDate, Duration.From(1), Duration.From(1)),

    // Add a custom column to fill missing dates

    CustomColumn = List.Generate(

        () => [DateIndex = 0, CurrentDate = MinDate],

        each [DateIndex] < List.Count(AllDates),

        each [DateIndex = [DateIndex] + 1, CurrentDate = AllDates{[DateIndex]}],

        each [CurrentDate]

    )

in

    CustomColumn

P7

 let

    // Assuming "Calendar3" is the name of your table and "StartDate" is the name of your date column

    // Create a list of all unique dates in the "StartDate" column

    DatesList = List.Sort(List.Distinct(Calendar3[StartDate])),

    // Find the minimum and maximum dates from the list

    MinDate = List.Min(DatesList),

    MaxDate = List.Max(DatesList),

    // Generate a list of all dates between the minimum and maximum dates

    AllDates = List.Dates(MinDate, Duration.From(1), Duration.From(1)),

    // Add a custom column to fill missing dates

    CustomColumn = List.Generate(

        () => [DateIndex = 0, CurrentDate = MinDate],

        each [DateIndex] < List.Count(AllDates),

        each [DateIndex = [DateIndex] + 1, CurrentDate = AllDates{[DateIndex]}],

        each [CurrentDate]

    ),

    FilledDates = Table.FromList(CustomColumn, Splitter.SplitByNothing()),

    // Merge the filled dates back into the "Calendar3" table

    MergedTable = Table.NestedJoin(Calendar3, {"StartDate"}, FilledDates, {"Column1"}, "Expanded", JoinKind.LeftOuter),

    // Expand the filled dates column

    ExpandedTable = Table.ExpandTableColumn(MergedTable, "Expanded", {"Column1"}, {"FilledDate"}),

    // Remove the temporary column used for the join

    CleanedTable = Table.RemoveColumns(ExpandedTable, {"Column1"})

in

    CleanedTable

P6

 let

    // Assuming "Calendar3" is the name of your table and "StartDate" is the name of your date column

    // Create a list of all unique dates in the "StartDate" column

    DatesList = List.Sort(List.Distinct(Calendar3[StartDate])),

    // Find the minimum and maximum dates from the list

    MinDate = List.Min(DatesList),

    MaxDate = List.Max(DatesList),

    // Generate a list of all dates between the minimum and maximum dates

    AllDates = List.Dates(MinDate, Duration.From(1), Duration.From(1)),

    // Add a custom column to fill missing dates

    CustomColumn = List.Generate(

        () => [DateIndex = 0, CurrentDate = MinDate],

        each [DateIndex] < List.Count(AllDates),

        each [DateIndex = [DateIndex] + 1, CurrentDate = AllDates{[DateIndex]}],

        each [CurrentDate]

    ),

    FilledDates = Table.FromList(CustomColumn, Splitter.SplitByNothing())

in

    Table.AddColumn(Calendar3, "FilledDate", each Fil

ledDates{[DateIndex]})

P5

 let

    // Assuming "Table" is the name of your table and "Date" is the name of your date column

    // Create a list of all unique dates in the "Date" column

    DatesList = List.Sort(List.Distinct(Table[Date])),

    // Find the minimum and maximum dates from the list

    MinDate = List.Min(DatesList),

    MaxDate = List.Max(DatesList),

    // Generate a list of all dates between the minimum and maximum dates

    AllDates = List.Dates(MinDate, Duration.From(1), Duration.From(1)),

    // Add a custom column to fill missing dates

    CustomColumn = List.Generate(

        () => [DateIndex = 0, CurrentDate = MinDate],

        each [DateIndex] < List.Count(AllDates),

        each [DateIndex = [DateIndex] + 1, CurrentDate = AllDates{[DateIndex]}],

        each [CurrentDate]

    ),

    FilledDates = Table.FromList(CustomColumn, Splitter.SplitByNothing())

in

    Table.AddColumn(Table, "FilledDate", each FilledDates{[DateIndex]})

P4

 let

    // Assuming "Table" is the name of your table and "Date" is the name of your date column

    // Find the previous date with a non-null value

    FillDates = (currentDate) =>

        let

            previousDate = List.LastN(Table.SelectRows(Table, each [Date] < currentDate and [Date] <> null), 1),

            result = if List.Count(previousDate) = 0 then currentDate else Record.Field(previousDate{0}, "Date")

        in

            result,

    // Fill null values based on the previous day's value

    CustomColumn = Table.AddColumn(Table, "FilledDate", each FillDates([Date])

in

  

  CustomColumn

P3

 let

    Source = CalendarTable,

    IterationsTable = IterationsTable,

    MergeCondition = Table.AddColumn(Source, "Merged", (ct) => 

        let

            calendarDate = ct[CalendarDate],

            matchingIterations = Table.SelectRows(IterationsTable, each [Start Date] <= calendarDate and [End Date] >= calendarDate and [Sprint Name] <> null),

            sprintNames = Text.Combine(matchingIterations[Sprint Name], ", ")

        in

            sprintNames, 

        type text

    ),

    Expanded = Table.ExpandTableColumn(MergeCondition, "Merged", {"Sprint Name"}, {"Sprint Name"})

in

    Expanded

P2

 = if [CalendarDate] >= List.Min(Table.SelectRows(IterationsTable, each [Start Date] <= c[CalendarDate] and [End Date] >= c[CalendarDate] and [Sprint Name] <> null)[Start Date]) and 

     [CalendarDate] <= List.Max(Table.SelectRows(IterationsTable, each [Start Date] <= c[CalendarDate] and [End Date] >= c[CalendarDate] and [Sprint Name] <> null)[End Date]) then 

     Text.Combine(Table.SelectRows(IterationsTable, each [Start Date] <= c[CalendarDate] and [End Date] >= c[CalendarDate] and [Sprint Name] <> null)[Sprint Name], ", ") 

   else null

p1

 = if [Date] >= List.Min(Table.SelectRows(SprintTable, each [StartDate] <= [Date] and [EndDate] >= [Date] and [SprintName] <> null)[StartDate]) and

[Date] <= List.Max(Table.SelectRows(SprintTable, each [StartDate] <= [Date] and [EndDate] >= [Date] and [SprintName] <> null)[EndDate]) then Text.Combine(Table.SelectRows(SprintTable, each [StartDate] <= [Date] and [EndDate] >= [Date] and [SprintName] <> null)[SprintName], ", ") else null ============= = if [Date] >= List.Min(Table.SelectRows(IterationsTable, each [Start Date] <= [Date] and [End Date] >= [Date] and [Sprint Name] <> null)[Start Date]) and [Date] <= List.Max(Table.SelectRows(IterationsTable, each [Start Date] <= [Date] and [End Date] >= [Date] and [Sprint Name] <> null)[End Date]) then Text.Combine(Table.SelectRows(IterationsTable, each [Start Date] <= [Date] and [End Date] >= [Date] and [Sprint Name] <> null)[Sprint Name], ", ") else null

Hello

let

    // Find the maximum car model from the Car Model table

    MaxCarModel = List.Max(CarTable[CardModel]),

    // Define the function to find the car model for each date

    FindCarModel = (date) =>

        let

            CarModelRow = Table.SelectRows(CarTable, each [Startdate] <= date and date <= [EndDate] and [CardModel] = MaxCarModel),

            CarModel = if Table.IsEmpty(CarModelRow) then MaxCarModel else Record.Field(CarModelRow{0}, "CardModel")

        in

            CarModel,

    // Add a custom column with the car model for each date

    CarModelColumn = Table.AddColumn(Calendar, "CarModel", each FindCarModel([Date]))

in

   

 CarModelColumn