Dataflow connecting to SharePoint Online Lists

[Anonymous]

Hello,

I have a dataflow that is connecting to 4 different SP lists, and then appending them. I've tried to create this in both Power Query and in dataflows, and am not able to get it to sucessfully load. 

The largest list is about 13k rows and doesn't need to be refreshed. How do I prevent the dataflow from refreshing that table? I've disabled load, but I think it's still trying to refresh the table each time.

I have had to extend several fields to get the lookup valudes - mostly on the people picker fields. if there's a better way, please let me know! I initially expanded out the "fields as text" record and removed everything else, but that was actually taking longer than just expanding the fileds that I needed.

The Last two rows were auto generated.

The BRQ2019 table is the one that doesn't need refreshed - just appended.

Thanks!

Kim

let

  Source = SharePoint.Tables("https://site"),

  #"Navigation 1" = Source{[Name = "BRQ"]}[Content],

  #"Expanded Entity" = Table.ExpandRecordColumn(#"Navigation 1", "Entity", {"Entity"}, {"Entity.1"}),

  #"Expanded Payer" = Table.ExpandRecordColumn(#"Expanded Entity", "Payer", {"Payer"}, {"Payer.1"}),

  #"Expanded PostingType" = Table.ExpandRecordColumn(#"Expanded Payer", "PostingType", {"PostingType"}, {"PostingType.PostingType"}),

  #"Expanded BatchType" = Table.ExpandRecordColumn(#"Expanded PostingType", "BatchType", {"BatchType"}, {"BatchType.BatchType"}),

  #"Expanded Analyst" = Table.ExpandTableColumn(#"Expanded BatchType", "Analyst", {"Name"}, {"Analyst"}),

  #"Expanded ReleasedBy" = Table.ExpandRecordColumn(#"Expanded Analyst", "ReleasedBy", {"Name"}, {"Released By"}),

  #"Expanded CreatedBy" = Table.ExpandRecordColumn(#"Expanded ReleasedBy", "CreatedBy", {"Name"}, {"Created By"}),

  #"Expanded ModifiedBy" = Table.ExpandRecordColumn(#"Expanded CreatedBy", "ModifiedBy", {"Name"}, {"Modified By"}),

  #"Choose columns" = Table.SelectColumns(#"Expanded ModifiedBy", {"CheckNumber", "RemitNumber", "DepositAmount", "BatchAmount", "TakebackAmount", "AdjustmentAmount", "PatientPayAmount", "Interest", "Balanced", "AutoPostBatchNumber", "AutoPostBatch", "PaymentApplicationInvoiceAndMemoBatchNumber", "BatchNumber", "DepositReference", "PeriodToPost", "DocumentNumber", "DocumentDate", "DateBatchCompleted", "AnalystNotes", "PaidClaimCount", "ShortPaidClaimCount", "DeniedClaimCount", "DeniedClaimAmount", "PendingClaimCount", "PendingClaimAmount", "UnappliedPaymentCount", "UnappliedPaymentAmount", "TotalClaimCount", "TotalAmount", "StatusValue", "DateBatchReleased", "ReleaseNotes", "DocuWareLink", "Created", "Modified", "Entity.1", "Payer.1", "PostingType.PostingType", "BatchType.BatchType", "Analyst", "Released By", "Created By", "Modified By"}),

  #"Renamed columns" = Table.RenameColumns(#"Choose columns", {{"Entity.1", "Entity"}, {"Payer.1", "Payer"}, {"PostingType.PostingType", "Posting Type"}, {"BatchType.BatchType", "Batch Type"}, {"CheckNumber", "Check Number"}, {"RemitNumber", "Remit Number"}, {"DepositAmount", "Deposit Amount"}, {"BatchAmount", "Batch Amount"}, {"TakebackAmount", "Takeback Amount"}, {"AdjustmentAmount", "Adjustment Amount"}, {"PatientPayAmount", "Patient Pay Amount"}}),

  #"Appended query 1" = Table.Combine({#"Renamed columns", BRQ2019}),

  #"Appended query 2" = Table.Combine({#"Appended query 1", BRQAKFCPrivatePay}),

  #"Appended query 3" = Table.Combine({#"Appended query 2", BRQCDVA}),

  #"Transform columns 1" = Table.TransformColumnTypes(#"Appended query 3", {{"AutoPostBatchNumber", type text}}),

  #"Replace errors 1" = Table.ReplaceErrorValues(#"Transform columns 1", {{"AutoPostBatchNumber", null}})

in

  #"Replace errors 1"

[Mariusz]

Hi @Anonymous 

If this table does not need refreshing and the rest do, then you can create a separate dataflow just for this table, refresh manually and later use it to build your normal dataflow.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn