Slow running Queries Power BI and Power Query

Used for anything related to the new Power BI Desktop and Service tools
mitchandsuzy
Posts: 19
Joined: Tue Nov 17, 2015 4:00 pm

Slow running Queries Power BI and Power Query

Postby mitchandsuzy » Thu Mar 09, 2017 9:03 am

Hi everyone,
This issue has perplexed me and despite making changes I still have a very slow refreshing table in Power Query & Power BI.
I have data extracted from SAP. I have no idea whether everyone using SAP has the same arrangement for data but I will explain what we have just in case.
We have what I call the “master level” followed by a “- 1” and a “-2” and sometimes more. Like a folder hierarchy.
When it is set up (I have been told) the data such as customer, customer address, post codes, classification, categories, project names.. etc are populated down all levels.
However as people start using the levels data gets changed and I can have a post code on “-1” and a different one on “-2”! This is clearly in error but I cannot tell which is the correct version.
The way I have gotten around this is to create two functions :-
One to select each individual column eg “Postal Code” which pivots the data and the joins all the non-empty data together eg 2017, 2032. Even project names can be different on the different levels.
Sample data
Hi everyone.docx
Sample of the data I need to manipulate
(58.22 KiB) Downloaded 37 times


So I end up with 25 tables (25 fixed columns) which are then joined back together to create a table. Each table is joined to the previous as soon as it is created which is hopefully saving memory.

Here is my function - it selects a particular column myParam out from Sourcename my table

Code: Select all

let
    tbl_Usage = (myParam as text, Sourcename as table) as table =>
let
    #"Filtered Rows" = Table.SelectRows(Sourcename, each ([Usage] = myParam)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Milestone Actual Date", myParam},{"Project Definition", "Project definition"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Milestone number", "Usage", "Milestone Description"}),
    #"Sorted Rows2" = Table.Sort(#"Removed Columns",{{"Project definition", Order.Ascending},{myParam, Order.Ascending}}),
    Base1 = Table.AddIndexColumn(#"Sorted Rows2","Index",1,1),
    Grouped1 = Table.Group(Base1, {"Project definition"}, {{"MaxIndex", each List.Max([Index]), type number}}),   
    Joined1 = Table.Join ( Base1, "Project definition", Grouped1, "Project definition", JoinKind.Inner ),
    #"Added Custom1a" = Table.AddColumn(Joined1, "Custom", each [MaxIndex] - [Index] + 1),
    #"Removed Columns1a" = Table.RemoveColumns(#"Added Custom1a",{"Index", "MaxIndex"}),
   
    Pivoted1 = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1a", {{"Custom", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1a", {{"Custom", type text}}, "en-US")[Custom]), "Custom", myParam),
   
    colList = Table.ColumnNames(Pivoted1),  // this creates list of all the columns columns
     
    #"Removed Top Items" = List.Skip(colList,1),   // Removes the "Project Definition" from the list allowing the max value to be found
     
    colListnum = Number.FromText(List.Max(#"Removed Top Items")),   // was using   Number.FromText(List.Last(#"Removed Top Items"))  but as thwe columns are in random order the highest number may not be the last value
   
    //  This is because the data is dates and you cannot concatenate dates
    MergedColumns1 =  if colListnum = 8 then
         Table.TransformColumnTypes(Pivoted1,{{"1", type text}, {"2", type text}, {"3", type text},{"4", type text}, {"5", type text}, {"6", type text}, {"7", type text}, {"8", type text}})
    else if colListnum = 7 then
         Table.TransformColumnTypes(Pivoted1,{{"1", type text}, {"2", type text}, {"3", type text},{"4", type text}, {"5", type text}, {"6", type text}, {"7", type text}})
    else if colListnum = 6 then
         Table.TransformColumnTypes(Pivoted1,{{"1", type text}, {"2", type text}, {"3", type text},{"4", type text}, {"5", type text}, {"6", type text}})
    else if colListnum = 5 then
         Table.TransformColumnTypes(Pivoted1,{{"1", type text}, {"2", type text}, {"3", type text},{"4", type text}, {"5", type text}})
    else if colListnum = 4 then
         Table.TransformColumnTypes(Pivoted1,{{"1", type text}, {"2", type text}, {"3", type text},{"4", type text}})
    else if colListnum = 3 then
         Table.TransformColumnTypes(Pivoted1,{{"1", type text}, {"2", type text}, {"3", type text}})
    else if colListnum = 2 then
         Table.TransformColumnTypes(Pivoted1,{{"1", type text}, {"2", type text}})
    else
         Table.TransformColumnTypes(Pivoted1,{{"1", type text}}),

    MergedColumns2 =  if colListnum = 8 then
         Table.CombineColumns(MergedColumns1,{ "1", "2", "3", "4", "5", "6", "7", "8"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
    else if colListnum = 7 then
         Table.CombineColumns(MergedColumns1,{ "1", "2", "3", "4", "5", "6", "7"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
    else if colListnum = 6 then
         Table.CombineColumns(MergedColumns1,{ "1", "2", "3", "4", "5", "6"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
    else if colListnum = 5 then
         Table.CombineColumns(MergedColumns1,{ "1", "2", "3", "4", "5"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
    else if colListnum = 4 then
         Table.CombineColumns(MergedColumns1,{ "1", "2", "3", "4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
    else if colListnum = 3 then
         Table.CombineColumns(MergedColumns1,{ "1", "2", "3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
    else if colListnum = 2 then
         Table.CombineColumns(MergedColumns1,{ "1", "2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
    else         
        Table.CombineColumns(MergedColumns1,{"1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),


    #"Added Custom1b" = Table.AddColumn(MergedColumns2, myParam, each Text.Trim ( [Merged], "," )),
    #"Removed Columns1b" = Table.RemoveColumns(#"Added Custom1b",{"Merged"})
   
in
    #"Removed Columns1b"

in
    tbl_Usage
   


and my join function is here

Code: Select all

let
    tbl_Usage = (myParam2 as text, Sourcename as table, myParam as table) as table =>
let

    #"Renamed Columns" = Table.RenameColumns(Sourcename,{{"Project definition", "pd1"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"pd1"},myParam,{"Project definition"},"NewColumn",JoinKind.FullOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Project definition", myParam2}, {"pd2", myParam2}),
    #"Added Custom" = Table.AddColumn(#"Expanded NewColumn", "Project definition", each if [pd1] = null then [pd2] else [pd1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"pd1", "pd2"})
in
    #"Removed Columns"
in
    tbl_Usage


All bought together with this

Code: Select all

let

    Source = #"Milestones for All 2",
    Source2 = #"Project Definition",

    Table0= fnPrepareUsageTables("COR01", Source),
    Joined0 = fnJoinSelectedTables("COR01", Source2, Table0),
   
    Table1= fnPrepareUsageTables("COR02", Source),
    Joined1 = fnJoinSelectedTables("COR02", Joined0,Table1),

    Table2= fnPrepareUsageTables("PAY01", Source),
    Joined2 = fnJoinSelectedTables("PAY01", Joined1,Table2),
   
    etc.... for 25 tables


Hopefully this is enough information for understanding but not too much .....

Thank you for taking the time and energy to read this.

Suzy

MattAllington
Posts: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Slow running Queries Power BI and Power Query

Postby MattAllington » Sat Mar 25, 2017 10:15 am

Interesting. thanks for the effort to explain. Im not sure I am clear on the problem or what you want to do different. Can you cut and paste a table into Excel and point your queries to this table so it is easy to see what you have done, or is that what you have posted already? I am on iPad so I can't see.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

mitchandsuzy
Posts: 19
Joined: Tue Nov 17, 2015 4:00 pm

Re: Slow running Queries Power BI and Power Query

Postby mitchandsuzy » Sun Mar 26, 2017 6:04 pm

Hi Matt,

Thanks for taking the time to understand my question.

I have just gone through and sanitized my data so I could send it through to you. I hope this helps explain this issue.

I need to do this because when I report I can never be sure that data I am using is the correct version, as we have multiple versions on the different "levels" in SAP. If everyone put the data in correctly I would not have to do this but because we are human......

I may not have an issue - maybe what I am trying to do is very time consuming - I very much doubt it but who knows.

Suzy
Attachments
Test Reporting.zip
Zipped PBIx file
(1.92 MiB) Downloaded 35 times
Test CN43N Details II.XLSX
This is the data file
(1.46 MiB) Downloaded 33 times


Return to “Power BI Desktop/Service”

Who is online

Users browsing this forum: No registered users and 1 guest