Exporting Data from Power Query into Multiple Sheet and merging steps into one.

earthworm

Well-known Member
Joined
May 19, 2009
Messages
766
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I have a data of 10.5 Million after I have made some consolidation and filteration in data inside power query.
Now i want to export the data into excel sheet whever data will split into multiple sheets if the data legnth exceeds the sheet max limit.
I tried Dax query but for that you need to import the data into data model and that take ages to load and then export to CVT / Text file.
Any fast solution please advice .

Secondly how to merge all the steps into single query so that it takes less time to load.

Please refer below code for easy reference
Power Query:
let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Name], "Filter")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Name] <> "Table25" and [Name] <> "Table26")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Name"}),
    #"Expanded Content" = Table.ExpandTableColumn(#"Removed Columns", "Content", {"REMITTERKEY", "MTO/ NON-MTO", "BANK NAME", "BANK COUNTRY", "PROCESSING_DATE", "UBL_UNIQUEID_NEW", "REMITTER_UNIQUEID_NEW", "SENDER_NAME", "BENEFICIARYNAME", "BENEFICIARY_ADDRESS", "BRANCHCODE", "ACCCOUNT_NO_NEW", "AMOUNT", "PROCESSING_REMARKS", "TYPE", "POSTING_DATE", "BANKCODE", "BANKNAME", "BENEFICIARYNICNO_NEW", "KYC_NIC_NO", "KYC_CONTACT_NO", "SENDER_EMAIL_ID_NEW", "SENDER_NO_NEW", "SENDER_PHONE_NO_NEW", "SENDER_POSTAL_CODE", "TCS_NO", "DISPATCH_DATE", "DELIVERY_DATE", "CREDITDATE", "SENDER_COUNTRY_2", "SENDER_COUNTRY", "COD"}, {"REMITTERKEY", "MTO/ NON-MTO", "BANK NAME", "BANK COUNTRY", "PROCESSING_DATE", "UBL_UNIQUEID_NEW", "REMITTER_UNIQUEID_NEW", "SENDER_NAME", "BENEFICIARYNAME", "BENEFICIARY_ADDRESS", "BRANCHCODE", "ACCCOUNT_NO_NEW", "AMOUNT", "PROCESSING_REMARKS", "TYPE", "POSTING_DATE", "BANKCODE", "BANKNAME", "BENEFICIARYNICNO_NEW", "KYC_NIC_NO", "KYC_CONTACT_NO", "SENDER_EMAIL_ID_NEW", "SENDER_NO_NEW", "SENDER_PHONE_NO_NEW", "SENDER_POSTAL_CODE", "TCS_NO", "DISPATCH_DATE", "DELIVERY_DATE", "CREDITDATE", "SENDER_COUNTRY_2", "SENDER_COUNTRY", "COD"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Content",{"MTO/ NON-MTO", "BANK COUNTRY"}),
    #"Filtered Rows2" = Table.SelectRows(#"Removed Columns1", each ([TYPE] = "D")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows2",{{"POSTING_DATE", type date}}),
    #"Filtered Rows3" = Table.SelectRows(#"Changed Type", each [PROCESSING_REMARKS] = "Account Credited"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows3",{{"PROCESSING_DATE", type date}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"UBL_UNIQUEID_NEW", "BENEFICIARY_ADDRESS"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns2",{{"AMOUNT", type number}, {"PROCESSING_REMARKS", type text}, {"TYPE", type text}, {"BANKCODE", type text}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Changed Type2",{"BANKNAME", "BENEFICIARYNICNO_NEW", "KYC_NIC_NO", "KYC_CONTACT_NO", "SENDER_EMAIL_ID_NEW", "SENDER_NO_NEW", "SENDER_PHONE_NO_NEW", "SENDER_POSTAL_CODE", "TCS_NO", "DISPATCH_DATE", "DELIVERY_DATE", "CREDITDATE", "SENDER_COUNTRY", "COD"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns3", "Custom", each Text.End([ACCCOUNT_NO_NEW],9)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Start([Custom],1)),
    #"Filtered Rows4" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = "2" or [Custom.1] = "3")),
    #"Removed Columns4" = Table.RemoveColumns(#"Filtered Rows4",{"Custom.1", "BANKCODE", "BRANCHCODE"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns4",{{"REMITTER_UNIQUEID_NEW", type text}, {"REMITTERKEY", type text}, {"BANK NAME", type text}, {"SENDER_NAME", type text}, {"BENEFICIARYNAME", type text}, {"ACCCOUNT_NO_NEW", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type3", {"Custom"}, Account, {"Content.ACCT_NO"}, "Account", JoinKind.LeftOuter),
    #"Expanded Account" = Table.ExpandTableColumn(#"Merged Queries", "Account", {"Content.ACCT_NO"}, {"Content.ACCT_NO"}),
    #"Filtered Rows5" = Table.SelectRows(#"Expanded Account", each [Content.ACCT_NO] <> null)
in
    #"Filtered Rows5"
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Personally, I would load the data to the data model and then export to CSV using DAX Studio.
I tried but the problem is due to various special characters in the data in some columns the output in csv format gets distorted
 
Upvote 0
I tried but the problem is due to various special characters in the data in some columns the output in csv format gets distorted
Then clean the data in Power Query before loading into the data model. There are options in PQ to clean special characters.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top