Export Data From Power Query into Multiple Excel Sheets based on criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1531774%22%20slang%3D%22en-US%22%3EExport%20Data%20From%20Power%20Query%20into%20Multiple%20Excel%20Sheets%20based%20on%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531774%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20find%20here%20below%20the%20structure%20of%20the%20data%20I%20am%20working%20on%2C%20I%20would%20like%20to%20get%20some%20help%20to%20find%20a%20way%20that%20when%20I%20export%20the%20data%20from%20Power%20Query%20I%20am%20able%20to%20achieve%202%20things.%3C%2FP%3E%3CP%3E1)%20The%20data%20for%20each%20area%20(%20Column%20State%20)%20goes%20into%20a%20separate%20Excel%20worksheet%20of%20the%20file.%20I%20have%20about%20300%2B%20areas%20so%20using%20the%20reference%20and%20add%20filter%20at%20state%20column%20may%20not%20be%20a%20smart%20way%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20Additionally%20it%20would%20be%20great%20that%20the%20sheet%20gets%20renamed%20to%20the%20area%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELook%20forward%20to%20some%20help%20on%20this%20please%3C%2FP%3E%3CP%3EKind%20Regards%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Query.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206279i29A5B8369F47D0BA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Query.JPG%22%20alt%3D%22Query.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1531774%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531813%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20Data%20From%20Power%20Query%20into%20Multiple%20Excel%20Sheets%20based%20on%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531813%22%20slang%3D%22en-US%22%3EWhich%20version%20of%20Excel%20are%20you%20using%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531866%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20Data%20From%20Power%20Query%20into%20Multiple%20Excel%20Sheets%20based%20on%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F733082%22%20target%3D%22_blank%22%3E%40DhritimanL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%2C%20PowerQuery%20doesn't%20support%20output%20of%20one%20query%20to%20multiple%20sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20version%20of%20Excel%20supports%20Dynamic%20Arrays%20and%20the%20FILTER%20function%2C%20you%20can%20use%20this%20code%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EOption%20Explicit%0A%0APublic%20Sub%20SplitTableToSheets()%0A%0ADim%20full_data_listobject%20As%20ListObject%0ADim%20selected_range%20As%20Range%0ADim%20selected_data%20As%20Variant%0ADim%20selected_data_header%20As%20String%0ADim%20items_sheet%20As%20Worksheet%0ADim%20items_range%20As%20Range%0ADim%20item%20As%20String%0ADim%20i%20As%20Integer%0ADim%20new_item_sheet%20As%20Worksheet%0A%0ADeleteSheetWithoutWarning%20%22items%22%0A%0ASet%20full_data_listobject%20%3D%20ThisWorkbook.Worksheets(%22full%20data%22).ListObjects(1)%0A%0A%0A'%20get%20the%20range%20currently%20selected%0ASet%20selected_range%20%3D%20Selection%0Aselected_data_header%20%3D%20Selection.Offset(-1%2C%200).Cells(1%2C%201)%0A%0ASet%20items_sheet%20%3D%20ThisWorkbook.Worksheets.Add%0A%0Aitems_sheet.Name%20%3D%20%22items%22%0A%0Aselected_range.Copy%0A%0Aitems_sheet.Range(%22A1%22).PasteSpecial%20xlValues%0A%0ASet%20items_range%20%3D%20items_sheet.Range(%22A1%22).CurrentRegion%0A%0Aitems_range.RemoveDuplicates%201%2C%20xlNo%0A%0A'loop%20through%20each%20item%20and%20create%20a%20new%20sheet%20with%20the%20filtered%20data%0AFor%20i%20%3D%201%20To%20items_range.Rows.Count%0A%20%20%20%20If%20items_range.Cells(i%2C%201)%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20item%20%3D%20items_range.Cells(i%2C%201)%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20DeleteSheetWithoutWarning%20item%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20Set%20new_item_sheet%20%3D%20ThisWorkbook.Worksheets.Add%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20With%20new_item_sheet%0A%20%20%20%20%20%20%20%20%20%20%20%20.Name%20%3D%20item%0A%20%20%20%20%20%20%20%20%20%20%20%20.Range(%22A1%22).Formula2%20%3D%20%22%3D%22%20%26amp%3B%20full_data_listobject.Name%20%26amp%3B%20%22%5B%23Headers%5D%22%0A%20%20%20%20%20%20%20%20%20%20%20%20.Range(%22A2%22).Formula2%20%3D%20%22%3DFILTER(%22%20%26amp%3B%20full_data_listobject.Name%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22%2C%22%20%26amp%3B%20full_data_listobject.Name%20%26amp%3B%20%22%5B%22%20%26amp%3B%20selected_data_header%20%26amp%3B%20%22%5D%20%3D%20%22%22%22%20%26amp%3B%20item%20%26amp%3B%20%22%22%22)%22%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20''optionally%20uncomment%20these%20two%20lines%20to%20paste%20the%20results%20as%20values%20in%20the%20destination%20sheet%0A%20%20%20%20%20%20%20%20%20%20%20%20'.Range(%22A1%22).CurrentRegion.Copy%0A%20%20%20%20%20%20%20%20%20%20%20%20'.Range(%22A1%22).PasteSpecial%20xlPasteValues%0A%20%20%20%20%20%20%20%20End%20With%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20Exit%20For%0A%20%20%20%20End%20If%0ANext%20i%0A%0A%0AEnd%20Sub%0A%0A%0APrivate%20Sub%20DeleteSheetWithoutWarning(sheet_name%20As%20String)%0A%0AApplication.DisplayAlerts%20%3D%20False%0AOn%20Error%20Resume%20Next%0AThisWorkbook.Worksheets(sheet_name).Delete%0AOn%20Error%20GoTo%200%0AApplication.DisplayAlerts%20%3D%20True%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20a%20column%20in%20your%20data%20that%20contains%20the%20items%20you%20want%20to%20split%20by%2C%20then%20run%20the%20SplitTableToSheets%20sub-procedure.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22splittabletosheets.gif%22%20style%3D%22width%3A%20927px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206283iF57FD62723DD5751%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22splittabletosheets.gif%22%20alt%3D%22splittabletosheets.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20benefit%20of%20this%20is%20that%20it%20will%20keep%20the%20sub-sheets%20up%20to%20date%20when%20the%20data%20on%20the%20main%20sheet%20refreshes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20test%20it%20with%20my%20dataset%20using%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20doesn't%20work%20for%20you%20because%20you%20don't%20have%20access%20to%20the%20FILTER%20function%2C%20let%20me%20know.%20There%20are%20other%20ways%20to%20do%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1533131%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20Data%20From%20Power%20Query%20into%20Multiple%20Excel%20Sheets%20based%20on%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533131%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725726%22%20target%3D%22_blank%22%3E%40OwenPrice%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20the%20help.%3C%2FP%3E%3CP%3EI%20am%20using%20office%20365%20and%20am%20able%20to%20use%20Dynamic%20arrays.%26nbsp%3BI%20tried%20using%20your%20sample%20sheet%20on%20my%20system%20and%20seem%20to%20have%20run%20into%20a%20error%20here.%3C%2FP%3E%3CP%3EI%20am%20not%20VBA%20aware%2C%20sharing%20here%20with%20you%20the%20debug%20screen%20shot%20for%20runtime%20error%201004.%3C%2FP%3E%3CP%3EDo%20please%20help%20further%20on%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20Regards%3C%2FP%3E%3CP%3EDhritiman%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Debug%201.JPG%22%20style%3D%22width%3A%20846px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206365i9595ED8022E64D39%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Debug%201.JPG%22%20alt%3D%22Debug%201.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1533155%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20Data%20From%20Power%20Query%20into%20Multiple%20Excel%20Sheets%20based%20on%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533155%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F733082%22%20target%3D%22_blank%22%3E%40DhritimanL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20make%20sure%20you%20have%20selected%20just%20the%20data%20rows%20in%20the%20column%20and%20not%20the%20entire%20column.%20It%20should%20be%20like%20this%20(i.e.%20the%20header%20is%20not%20selected)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22OwenPrice_0-1595253082284.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206366i3B7EB947DB7CCED5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22OwenPrice_0-1595253082284.png%22%20alt%3D%22OwenPrice_0-1595253082284.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ETo%20do%20this%2C%20you%20hover%20your%20cursor%20over%20the%20column%20header%20until%20you%20see%20the%20black%20arrow%20to%20select%20the%20column%2C%20then%20select%20and%20it%20will%20just%20select%20the%20data%20and%20not%20the%20header.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20wrong%20(entire%20column%20is%20selected)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22OwenPrice_1-1595253103785.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206367iAC2BF99D30CB49E2%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22OwenPrice_1-1595253103785.png%22%20alt%3D%22OwenPrice_1-1595253103785.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20and%20let%20me%20know%20how%20you%20get%20on.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1533330%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20Data%20From%20Power%20Query%20into%20Multiple%20Excel%20Sheets%20based%20on%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533330%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725726%22%20target%3D%22_blank%22%3E%40OwenPrice%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%20Works%20just%20the%20way%20its%20expected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMuch%20Appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%3C%2FP%3E%3CP%3EDhritiman%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Please find here below the structure of the data I am working on, I would like to get some help to find a way that when I export the data from Power Query I am able to achieve 2 things.

1) The data for each area ( Column State ) goes into a separate Excel worksheet of the file. I have about 300+ areas so using the reference and add filter at state column may not be a smart way to work.

 

2) Additionally it would be great that the sheet gets renamed to the area name.

 

Look forward to some help on this please

Kind Regards

Query.JPG

5 Replies
Which version of Excel are you using?

@DhritimanL 

 

Unfortunately, PowerQuery doesn't support output of one query to multiple sheets.

 

If your version of Excel supports Dynamic Arrays and the FILTER function, you can use this code:

Option Explicit

Public Sub SplitTableToSheets()

Dim full_data_listobject As ListObject
Dim selected_range As Range
Dim selected_data As Variant
Dim selected_data_header As String
Dim items_sheet As Worksheet
Dim items_range As Range
Dim item As String
Dim i As Integer
Dim new_item_sheet As Worksheet

DeleteSheetWithoutWarning "items"

Set full_data_listobject = ThisWorkbook.Worksheets("full data").ListObjects(1)


' get the range currently selected
Set selected_range = Selection
selected_data_header = Selection.Offset(-1, 0).Cells(1, 1)

Set items_sheet = ThisWorkbook.Worksheets.Add

items_sheet.Name = "items"

selected_range.Copy

items_sheet.Range("A1").PasteSpecial xlValues

Set items_range = items_sheet.Range("A1").CurrentRegion

items_range.RemoveDuplicates 1, xlNo

'loop through each item and create a new sheet with the filtered data
For i = 1 To items_range.Rows.Count
    If items_range.Cells(i, 1) <> "" Then
        item = items_range.Cells(i, 1)
        
        DeleteSheetWithoutWarning item
        
        Set new_item_sheet = ThisWorkbook.Worksheets.Add
        
        With new_item_sheet
            .Name = item
            .Range("A1").Formula2 = "=" & full_data_listobject.Name & "[#Headers]"
            .Range("A2").Formula2 = "=FILTER(" & full_data_listobject.Name & _
                                            "," & full_data_listobject.Name & "[" & selected_data_header & "] = """ & item & """)"

            ''optionally uncomment these two lines to paste the results as values in the destination sheet
            '.Range("A1").CurrentRegion.Copy
            '.Range("A1").PasteSpecial xlPasteValues
        End With
        
    Else
        Exit For
    End If
Next i


End Sub


Private Sub DeleteSheetWithoutWarning(sheet_name As String)

Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets(sheet_name).Delete
On Error GoTo 0
Application.DisplayAlerts = True

End Sub

 

Select a column in your data that contains the items you want to split by, then run the SplitTableToSheets sub-procedure.

 

splittabletosheets.gif

 

 

The benefit of this is that it will keep the sub-sheets up to date when the data on the main sheet refreshes.

 

You can test it with my dataset using the attached file.

 

If this doesn't work for you because you don't have access to the FILTER function, let me know. There are other ways to do it.

 

@OwenPrice 

Thank you the help.

I am using office 365 and am able to use Dynamic arrays. I tried using your sample sheet on my system and seem to have run into a error here.

I am not VBA aware, sharing here with you the debug screen shot for runtime error 1004.

Do please help further on this.

 

Kind Regards

Dhritiman

Debug 1.JPG

@DhritimanL 

 

Please make sure you have selected just the data rows in the column and not the entire column. It should be like this (i.e. the header is not selected):

 

OwenPrice_0-1595253082284.png

To do this, you hover your cursor over the column header until you see the black arrow to select the column, then select and it will just select the data and not the header.

 

This is wrong (entire column is selected):

 

OwenPrice_1-1595253103785.png

 

Please try this and let me know how you get on.

@OwenPrice 

Thank you. Works just the way its expected.

 

Much Appreciated.

 

Kind regards

Dhritiman