Unpivot Issue. missing data

[markcabantog]

i have a an excel file like this

it contains 1,200,000 rows of data, (44MB file approx)

when make unpivot of "jan,feb,...dec" it works

then i make a filter on "sub type" of "electric"

then when i want to filter "type" only "car" is showing and "load more" is not there

can anyone help me

[kcantor]

@markcabantog 

I am not receiving the same error as you. I manually created data to match your display and moved through the steps. I did move the sub type during the query and renamed the attribute column to Month. Here is my query and pics of the process and result:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction date", type datetime}, {"type", type text}, {"jan", Int64.Type}, {"feb", Int64.Type}, {"mar", Int64.Type}, {"apr", Int64.Type}, {"may", Int64.Type}, {"jun", Int64.Type}, {"july", Int64.Type}, {"aug", Int64.Type}, {"sept", Int64.Type}, {"oct", Int64.Type}, {"nov", Int64.Type}, {"dec", Int64.Type}, {"sub type", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Transaction date", "type", "sub type", "jan", "feb", "mar", "apr", "may", "jun", "july", "aug", "sept", "oct", "nov", "dec"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Transaction date", "type", "sub type"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Month"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([sub type] = "electric"))
in
#"Filtered Rows"

[markcabantog]

this is sample data only.. try to put 1,200,000 of rows with more type and sub type..

[kcantor]

@markcabantog 

It should not matter how may rows you have. Most of my queries are well into the 2m range. Sometimes the preview window does not show all of the data. This is especially true when working with all files from a folder. You can choose to close and load and then use the filters within your data model as a true test of what loaded or you can reenter Query Editor and manually filter the data. 

I used the sample data your showed on the screen. If you have a file that you can share, I would be happy to take a look at a larger sample or your query.

[kcantor]

@markcabantog 

I spent a little time this morning creating a larger sample with random data. As you can see from the snip, this still worked appropriately. Granted, I did only create 500,000 rows but this still performs as expected. 

[markcabantog · Accepted solution]

Thank you all for the response...i found out that there is an issue in the Excel file. its working fine now.

[Anonymous]

The Power Query window doesn't load all your data, only a tiny sample.  Its not uncommon for it to not display things like this.  Unfortunately this means you might need to do some of the coding yourself and accept you won't see a preview.  You can still often get it to write the code for you by selecting a filter value that it can see, then simply changing the text yourself.

As a true test, click the apply button and see what data arrives.