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

[earthworm]

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

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"

 

[Matt Allington]

Personally, I would load the data to the data model and then export to CSV using DAX Studio.

 

[earthworm]

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

 

[Matt Allington]

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.