How to query from the data model and table those results

[Guitarmageddon]

So I have been getting my feet wet into the excel data model/power query/power pivot features, but Im quite a newb. I have a folder of like-files that I am able to combine/edit/and load into the data model. Its line count far exceeds what you could put on a single sheet (probably 2.5 million maybe). It is product data of every timestamp and location where an inventory change occurred. We sometimes want to investigate a single item out of all this data and see a historical table of all the changes line by line. Basically, this can be accomplished if all the data was in a single flat table and you just inputted that item number into the column filter. However, since the lines are too many, I cant just go straight to a sheet. How am I able to basically make a query to filter out (in a user friendly way) a single item at a time and display those results in a normal table? This way, others without the need to enter power query/data model can access.

 

[horseyride]

If I had to do this in powerquery

1. Create 2x2 named range called "Parameters". (We will use AAAA as what we will filter for using variable name SearchFor)

Parameter	Value
SearchFor	AAAA

<tbody>
</tbody>

2. Create function to read #1 called "GetValue". You could directly reference the cell, but this is easier if you ever want to add more variables later

(ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in Value

3. Create query ReadIt to read the variable. Doing this as separate step to #4 will avoid firewall error

let   Source =     GetValue("SearchFor") in Source

4. Create query to combine MegaTable (query with your existing data) and filter for the results of the variable
example

let
    Source = MegaList,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Dept] = ReadIt))
in #"Filtered Rows"

5. Return #4 as a table in excel
6. Change the variable when you need to, and refresh #5 table, and you should be good to go

 

[Guitarmageddon]

Wow this is awesome. Im going to give this a whirl but may have some elementary questions soon

If I had to do this in powerquery

1. Create 2x2 named range called "Parameters". (We will use AAAA as what we will filter for using variable name SearchFor)

Parameter	Value
SearchFor	AAAA

<tbody>
</tbody>

2. Create function to read #1 called "GetValue". You could directly reference the cell, but this is easier if you ever want to add more variables later

(ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in Value

3. Create query ReadIt to read the variable. Doing this as separate step to #4 will avoid firewall error

let   Source =     GetValue("SearchFor") in Source

4. Create query to combine MegaTable (query with your existing data) and filter for the results of the variable
example

let
    Source = MegaList,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Dept] = ReadIt))
in #"Filtered Rows"

5. Return #4 as a table in excel
6. Change the variable when you need to, and refresh #5 table, and you should be good to go

 

[Guitarmageddon]

Ok yep Im not sure where to start with entering these functions...can you provide any more help? Sorry its so elementary Im sure, but Im a bit clueless right now. You also said "if I was to do it in power query"- how else would you do it? Considering I am using power query to combine/edit the files, is there an easier way to search through the "master combined" table I have?

 

[horseyride]

for (1) its in excel directly, nothing to do with PowerQuery, and use some real world filter criteria instead of AAAAA for the value
In my version of excel, you can paste in the PowerQuery code as follows:
Data .. Get Data ... From Other Sources ... Blank Query ...
Home ... Advanced Editor ...
<Paste>
Then rename query as appropriate
Query names would be (2) GetValue (3) ReadIt (4) anything
Repeat above steps as needed for all queries (2,3,4) but for #4 reference your own table instead of Megalist and filter for appropriate column name instead of [Dept]

 

[Guitarmageddon]

for (1) its in excel directly, nothing to do with PowerQuery, and use some real world filter criteria instead of AAAAA for the value
In my version of excel, you can paste in the PowerQuery code as follows:
Data .. Get Data ... From Other Sources ... Blank Query ...
Home ... Advanced Editor ...


Then rename query as appropriate
Query names would be (2) GetValue (3) ReadIt (4) anything
Repeat above steps as needed for all queries (2,3,4) but for #4 reference your own table instead of Megalist and filter for appropriate column name instead of [Dept]

So if Im filtering my source data based on a column "RMS STYLE", how would I rename your formulas? And the original table? Would I change "value" to "RMS STYLE"?
</paste>

 

[horseyride]

So if Im filtering my source data based on a column "RMS STYLE", how would I rename your formulas? And the original table? Would I change "value" to "RMS STYLE"?

let
    Source = MegaList,
    #"Filtered Rows" = Table.SelectRows(Source, each ([RMS_STYLE] = ReadIt))
in #"Filtered Rows"

but you would need to replace MegaList with the name of the query storing your data as well