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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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