Power Query Not Loading All Rows

[asd2525]

I have tried to see if anyone else has had this issue but can't find anything. I have a query (using Power Query) where I am pulling from hundreds of files into a single file. What happens is there should be around 420,000 rows and it will show that there are that many before I do the refresh. Then when I do the refresh it will show that only 270,000-ish (will vary by several thousand) rows are loaded with no errors. The only way I have been able to fix the issue so far has been to just keep refreshing until all the rows are loaded. Does anyone know what is happening that would cause this or how I can ensure that it is loading right? I have this report built for several people and they probably wouldn't even realize some of the data is missing sometimes and can make really bad decisions if they don't have all the data. 

[asd2525 · Accepted solution]

Thanks everyone for the help. Nothing I was trying would work. I belive I did find the solution though. I had compiled a lot of files into a couple very large csv files that I was continually adding information to. These had data from 2014 to now and were of 80 MB in size. So in order to fix it I went and broke those files up by year and now the biggest one is a little over 23 MB. After doing that I have refreshed multiple times and have not had the issue again. I think the end reason was that because the files were so large and they were saved on Sharepoint that when the query was ran it would sometimes "timeout" in pulling the data. We deal with very sensitive data so I can see that a security measure could be put in place to prevent unauthorized downloads of data.

So, if anyone else has the same issue it might be because the Excel files they are querying are just too large and need to be broken up some. 

[Jimmy801]

Hello @asd2525 

could you share the code how your are combining theses hundreds of files? Do you use some custom functions?

BR

Jimmy

[asd2525]

Hey sorry I am pretty new to this whole BI thing and am entirely self taught. So what I have probably looks pretty ugly. Also, I am not entirely sure which code you are asking for so I just copied what was in the advanced editor. I have 4 seperat queries that I have merged into a single query. I will paste what was done after. Please let me know if you were asking for something else. Thank you for your help.

let

    Source = SharePoint.Files("XXXXXXXXXXXXXXXXXXXXXXX", [ApiVersion = 15]),

    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "Master Drug List")),

    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from NDCs", each #"Transform File from NDCs"([Content])),

    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from NDCs"}),

    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from NDCs", Table.ColumnNames(#"Transform File from NDCs"(#"Sample File"))),

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Product", type text}, {"NDC", type text}, {"Med Type", type text}, {"Category", type text}, {"Special Naming", type any}}),

    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"})

in

    #"Removed Columns"

let

    Source = SharePoint.Files("XXXXXXXXXXXXXXXXXXXXXXXXXXXXX", [ApiVersion = 15]),

    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "Insurance - CPC Master List")),

    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from ThirdParties", each #"Transform File from ThirdParties"([Content])),

    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from ThirdParties"}),

    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from ThirdParties", Table.ColumnNames(#"Transform File from ThirdParties"(#"Sample File (2)"))),

    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Source.Name"})

in

    #"Removed Columns"

let

    Source = SharePoint.Files("XXXXXXXXXXXXXXXXXXXXXXX", [ApiVersion = 15]),

    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "PrescriptionFillLogReport")),

    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from RxFillLog", each #"Transform File from RxFillLog"([Content])),

    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from RxFillLog"}),

    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from RxFillLog", Table.ColumnNames(#"Transform File from RxFillLog"(#"Sample File (3)"))),

    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Source.Name"}),

    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),

    #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each not Text.Contains([#"Fill #"], "Fill #")),

    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Pt Name", "Product", "Dosage Form", "NDC", "Sch", "CS #", "DE Init", "Disp Init", "Ver Rph", "TP ID", "Tot Amt Due", "Pt Amt Due", "Cost"}),

    #"Added Custom" = Table.AddColumn(#"Removed Columns1", "Fill IDs", each Text.PadStart(Text.From([#"Rx #"]),7,"0")&Text.PadStart(Text.From([#"Fill #"]),2,"0")),

    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Written Date", type date}, {"Fill Date", type date}, {"Fst Fill Date", type date}, {"Wqty", type number}, {"Dqty", type number}, {"RA", type number}, {"RR", type number}}),

    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Fill IDs"})

in

    #"Removed Duplicates"

let

    Source = SharePoint.Files("XXXXXXXXXXXXXXXXXXXXXXXXXXX", [ApiVersion = 15]),

    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "CloseOfBusinessDetailReport")),

    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from MasterTable-COB", each #"Transform File from MasterTable-COB"([Content])),

    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from MasterTable-COB"}),

    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from MasterTable-COB", Table.ColumnNames(#"Transform File from MasterTable-COB"(#"Sample File (4)"))),

    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Source.Name", "store_num", "generic", "Billing_Method", "SERVICE_BILLING_INDICATOR", "Partial_Fill_Seq", "Prior Auth"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Sold Date", type date}, {"Date_of_Service", type date}, {"Adjudication_Complete", type date}, {"QTY", type number}, {"TP_Total_Amt_Paid", Currency.Type}, {"PT_Pay_Amt_Total", Currency.Type}, {"Total_Claim_Price", Currency.Type}, {"acq_cost", Currency.Type}, {"Gross_Amount_Due", Currency.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Fill IDs", each Text.PadStart(Text.From([rx_number]),7,"0")&Text.PadStart(Text.From([refill_num]),2,"0")),

    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"NDC_num"},NDCs,{"NDC"},"NDCs",JoinKind.LeftOuter),

    #"Expanded NDCs" = Table.ExpandTableColumn(#"Merged Queries", "NDCs", {"Med Type", "Category", "Special Naming"}, {"Med Type", "Category", "Special Naming"}),

    #"Renamed Columns" = Table.RenameColumns(#"Expanded NDCs",{{"Category", "Med Category"}, {"Special Naming", "Special Med Naming"}}),

    #"Merged Queries1" = Table.NestedJoin(#"Renamed Columns",{"TP_Name"},ThirdParties,{"TP_Name"},"ThirdParties",JoinKind.LeftOuter),

    #"Expanded ThirdParties" = Table.ExpandTableColumn(#"Merged Queries1", "ThirdParties", {"Coupon?", "Comm/Mcaid/Mcare"}, {"Coupon?", "Comm/Mcaid/Mcare"}),

    #"Renamed Columns2" = Table.RenameColumns(#"Expanded ThirdParties",{{"Coupon?", "Third Party Type"}, {"Comm/Mcaid/Mcare", "Third Party Category"}}),

    #"Merged Queries2" = Table.NestedJoin(#"Renamed Columns2",{"Fill IDs"},RxFillLog,{"Fill IDs"},"RxFillLog",JoinKind.LeftOuter),

    #"Expanded RxFillLog" = Table.ExpandTableColumn(#"Merged Queries2", "RxFillLog", {"Pt Address", "Wqty", "Dqty", "RA", "RR", "Presc Name", "Presc Address", "DEA", "Written Date", "Fill Date", "Fst Fill Date"}, {"Pt Address", "Wqty", "Dqty", "RA", "RR", "Presc Name", "Presc Address", "DEA", "Written Date", "Fill Date", "Fst Fill Date"}),

    #"Renamed Columns3" = Table.RenameColumns(#"Expanded RxFillLog",{{"Pt Address", "Patient Address"}, {"Wqty", "Written Quantity"}, {"Dqty", "Dispensed Quantity"}, {"RA", "Refills Allowed"}, {"RR", "Refills Remaining"}, {"Presc Name", "Prescriber Name"}, {"Presc Address", "Prescriber Address"}, {"Fst Fill Date", "First Fill Date"}, {"rx_number", "Prescription Number"}, {"refill_num", "Refill Number"}, {"Adjudication_Complete", "Adjudication Complete Date"}, {"QTY", "Quantity"}, {"NDC_num", "NDC"}, {"Patient", "Patient Name"}, {"TP_Code", "Third Party Code"}, {"TP_Name", "Third Party Name"}, {"COB_INDICATOR", "COB Indicator"}, {"TP_Total_Amt_Paid", "Total Amount Third Party Paid"}, {"PT_Pay_Amt_Total", "Total Amount Patient Paid"}, {"Total_Claim_Price", "Total Claim Price"}, {"acq_cost", "Acquisition Cost"}, {"Date_of_Service", "Date of Service"}, {"Gross_Amount_Due", "Gross Amount Due - incorrect do not use"}})

in

    #"Renamed Columns3"

[Jimmy801]

Hello @asd2525 

were you able to solve the problem with any reply given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[Jimmy801]

Hello @asd2525 

Are this queries working? Your are adding adding columns where you reference / invoke this funciton #"Transform File from NDCs"([Content]) that can't be a function, because function can't contain the charachters. And I even can find a step named like this. So what are you referencing here? I suppose that you would like to apply custom functions depending on the file type. So could you post one query of your custom function?

And when you are taking about having 270k rows instead of 420k... do you need to combine these data somewhere or do you have 4 different tables?

Have a nice day

Jimmy