Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Power Query Efficiency

Hi, 


I am trying to improve the effiency of the query below.

 

The database I am reaching is huge but the data I need is not large. I thought that adding the filters in the end would have improved performance but it still thanks a lot of time to to update the query or refresh the data. 

 

I am probably missing some basic good practice to get it working properly.

 

I cannot do a live link to the dataset as some of the data will have to be transformed afterwards.

 

Thanks in advance.

 

 

let
    Source = AnalysisServices.Databases("link://xxx.azurewebsites.net", [TypedMeasureColumns=true, Implementation="2.0"]),
    #"MS - Orders1" = Source{[Name="MS - Orders"]}[Data],
    #"MS - Orders2" = #"MS - Orders1"{[Id="MS - Orders"]}[Data],
    #"MS - Orders3" = #"MS - Orders2"{[Id="MS - Orders"]}[Data],
    #"Added Items1" = Cube.Transform(#"MS - Orders3",
        {
            {Cube.AddAndExpandDimensionColumn, "[Dim_Customers]", {"[Dim_Customers].[Hrchy_Customers].[Billing Account]", "[Dim_Customers].[Hrchy_Customers].[Corporate Account]", "[Dim_Customers].[Hrchy_Customers].[Sales Account]"}, {"Dim_Customers.Billing Account.3", "Dim_Customers.Corporate Account.3", "Dim_Customers.Sales Account.2"}},
            {Cube.AddAndExpandDimensionColumn, "[Dim_Date]", {"[Dim_Date].[Date].[Date]", "[Dim_Date].[Month].[Month]", "[Dim_Date].[Month (Date)].[Month (Date)]", "[Dim_Date].[Month#].[Month#]", "[Dim_Date].[Quarter].[Quarter]", "[Dim_Date].[Quarter#].[Quarter#]", "[Dim_Date].[Year].[Year]", "[Dim_Date].[Year (Date)].[Year (Date)]", "[Dim_Date].[YearMonth].[YearMonth]", "[Dim_Date].[YearMonth (PI Display)].[YearMonth (PI Display)]", "[Dim_Date].[YearQuarter].[YearQuarter]", "[Dim_Date].[Hrchy_Date].[Year (Date)]", "[Dim_Date].[Hrchy_Date].[Month (Date)]", "[Dim_Date].[Hrchy_Year_Date].[Year]", "[Dim_Date].[Hrchy_Year_Date].[YearQuarter]", "[Dim_Date].[Hrchy_Year_Date].[YearMonth]"}, {"Dim_Date.Date.1", "Dim_Date.Month.1", "Dim_Date.Month (Date).3", "Dim_Date.Month#.1", "Dim_Date.Quarter.1", "Dim_Date.Quarter#.1", "Dim_Date.Year.3", "Dim_Date.Year (Date).3", "Dim_Date.YearMonth.3", "Dim_Date.YearMonth (PI Display).1", "Dim_Date.YearQuarter.3", "Dim_Date.Year (Date).2", "Dim_Date.Month (Date).2", "Dim_Date.Year.2", "Dim_Date.YearQuarter.2", "Dim_Date.YearMonth.2"}},
            {Cube.AddAndExpandDimensionColumn, "[Dim_Product_Line]", {"[Dim_Product_Line].[Product Line (Code)].[Product Line (Code)]", "[Dim_Product_Line].[Product Line (Desc)].[Product Line (Desc)]"}, {"Dim_Product_Line.Product Line (Code).1", "Dim_Product_Line.Product Line (Desc).1"}},
            {Cube.AddAndExpandDimensionColumn, "[Dim_xxx_Organization]", {"[Dim_xxx_Organization].[Country (Code)].[Country (Code)]", "[Dim_xxx_Organization].[Country (Desc)].[Country (Desc)]", "[Dim_xxx_Organization].[Geomarket (Code)].[Geomarket (Code)]", "[Dim_xxx_Organization].[Geomarket (Desc)].[Geomarket (Desc)]"}, {"Dim_xxx_Organization.Country (Code).1", "Dim_xxx_Organization.Country (Desc).1", "Dim_xxx_Organization.Geomarket (Code).1", "Dim_xxx_Organization.Geomarket (Desc).1"}},
            {Cube.AddAndExpandDimensionColumn, "[Fact_MandS_Orders]", {"[Fact_MandS_Orders].[Field Ticket Ref #].[Field Ticket Ref #]", "[Fact_MandS_Orders].[FTL Approval Date].[FTL Approval Date]", "[Fact_MandS_Orders].[FTL Approver ID].[FTL Approver ID]", "[Fact_MandS_Orders].[FTL Submission Date].[FTL Submission Date]", "[Fact_MandS_Orders].[iDistrict/eTrace#].[iDistrict/eTrace#]", "[Fact_MandS_Orders].[Invoice Create Date].[Invoice Create Date]", "[Fact_MandS_Orders].[Invoice Post Date].[Invoice Post Date]", "[Fact_MandS_Orders].[Order Approval Date].[Order Approval Date]", "[Fact_MandS_Orders].[Order Creation Date].[Order Creation Date]", "[Fact_MandS_Orders].[Order Creator Job Category].[Order Creator Job Category]", "[Fact_MandS_Orders].[Order First Submission Date].[Order First Submission Date]", "[Fact_MandS_Orders].[Order Job End Date].[Order Job End Date]", "[Fact_MandS_Orders].[Order Type].[Order Type]", "[Fact_MandS_Orders].[Revenue Status].[Revenue Status]", "[Fact_MandS_Orders].[Siebel Order ID].[Siebel Order ID]", "[Fact_MandS_Orders].[Stage 1].[Stage 1]", "[Fact_MandS_Orders].[Stage 5].[Stage 5]", "[Fact_MandS_Orders].[Stage_2].[Stage_2]", "[Fact_MandS_Orders].[Stage_3].[Stage_3]", "[Fact_MandS_Orders].[Stage_4].[Stage_4]"}, {"Fact_MandS_Orders.Field Ticket Ref #.1", "Fact_MandS_Orders.FTL Approval Date.1", "Fact_MandS_Orders.FTL Approver ID.1", "Fact_MandS_Orders.FTL Submission Date.1", "Fact_MandS_Orders.iDistrict/eTrace#", "Fact_MandS_Orders.Invoice Create Date.1", "Fact_MandS_Orders.Invoice Post Date", "Fact_MandS_Orders.Order Approval Date.1", "Fact_MandS_Orders.Order Creation Date.1", "Fact_MandS_Orders.Order Creator Job Category", "Fact_MandS_Orders.Order First Submission Date", "Fact_MandS_Orders.Order Job End Date.1", "Fact_MandS_Orders.Order Type.1", "Fact_MandS_Orders.Revenue Status.1", "Fact_MandS_Orders.Siebel Order ID.1", "Fact_MandS_Orders.Stage 1.1", "Fact_MandS_Orders.Stage 5.1", "Fact_MandS_Orders.Stage_2.1", "Fact_MandS_Orders.Stage_3.1", "Fact_MandS_Orders.Stage_4.1"}},
            {Cube.AddMeasureColumn, "Revenue (kUSD).1", "[Measures].[Revenue (kUSD)]"},
            {Cube.AddMeasureColumn, "Stage 2.1", "[Measures].[Stage 2]"},
            {Cube.AddMeasureColumn, "Stage 3.1", "[Measures].[Stage 3]"},
            {Cube.AddMeasureColumn, "Stage 4.1", "[Measures].[Stage 4]"},
            {Cube.AddMeasureColumn, "Total DIR Stage.1", "[Measures].[Total DIR Stage]"}
        }),
    #"Filtered Rows" = Table.SelectRows(#"Added Items1", each (Cube.AttributeMemberId([Dim_Date.Year.2]) = "[Dim_Date].[Hrchy_Year_Date].[Year].&[2019]" meta [DisplayName = "2019"]) and (Cube.AttributeMemberId([Fact_MandS_Orders.Revenue Status.1]) = "[Fact_MandS_Orders].[Revenue Status].&[Invoiced]" meta [DisplayName = "Invoiced"]) and (Cube.AttributeMemberId([Fact_MandS_Orders.Order Type.1]) = "[Fact_MandS_Orders].[Order Type].&[Sales Order]" meta [DisplayName = "Sales Order"])),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [#"Revenue (kUSD).1"] <> null and [#"Revenue (kUSD).1"] <> "")
in
    #"Filtered Rows1"

 

1 REPLY 1
TeigeGao
Solution Sage
Solution Sage

Hi @Anonymous ,

I would suggest you handling these data at data source rather than in PowerBI, you can add filters in data source, if you add it in Power Query, these data has been imported into PowerBI.

Best Regards,

Teige

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.