earthworm
Well-known Member
- Joined
- May 19, 2009
- Messages
- 766
- Office Version
- 2019
- 2016
- Platform
- 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
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"
