(Dataverse) Selective Power query?

[Sunbed1060]

Hi,

I am new to using Dataverse and power query.

I am currently use two excel files - one for material data source and other for partlist.

So far, no trouble, but with growing number of materials.
I want to change the excel data source to Dataverse.

Background:

• Using an excel file as main material data source.
• Other excel file uses XLOOKUP to find the item code to pull item details.
• Material data is growing (so far 5,000 rows). 
• Thinking to change the data source to Dataverse.

Questions:

• I understand by creating query connection - the data needs to load into the target file then I can use XLOOKUP to query table.
• But I want it to selective - instead of all 5,000 data to be pulled into file.
• Can the power query be selective based on the item code that was used on the partlist?
  *like below image
  
  Selective powerquery?

Thank you in an advance.

[#2 · Accepted solution]

@Sunbed1060 

You may filter on partlist

as

let
    Result = Table.SelectRows(Source, each List.Contains( Partlist[ID], [ID]  ))
in
    Result

[#3]

Thank you Sergei,

If I may ask, where do I insert the above code?

[#4]

@Sunbed1060 

In Power Query editor you may open Advanced Editor to modify M-script code, or for the concrete step you may modify the code in formula bar of the PQ editor.

[#5]

Thank you @Sergei Baklan  ,

It is working as well :)

For the table name for Partlist, can it be dynamic?
As of right now, we are using the partlist excel file as teams template file that can be used by other users.

And table name tends to get messy.

[#6]

@Sunbed1060 

It could be, but it depends on which logic do you use to select this or that partlist table.

As variant that name of the file/table could be defined in the cell of Excel file which we could read by Power Query and use as parameter. Lot of samples, e.g.

Building a Parameter Table for Power Query (excelguru.ca)

#PowerQuery – Dynamically reference a table via a Parameter – Erik Svensen – Blog about Power BI, Po...

[#7]

Thank you
After going through and re-create the examples on links.

It might be too difficult for other users to follow through.

However, I definitely got more understanding where to look into more.

Thank you :)

[#8]

@Sunbed1060 

In general the only what other users shall do is to enter desired partlist table name into the cell. Or you may them give an option to select from the list.

The rest is done by Power Query. However, all depends on details of your business process.

[#9]

Hi @Sergei Baklan,

If I may ask how to do two actions you have described?

1. User input the table name in cell.

2. User selecting the table name from a list.

To explain furthermore on the partlist file.

• Users will create a new partlist file through team's template (from NEW).
• The new partlist file will have two premade sheets - which user can delete or copy to make more sheet based on their needs.
  *meanwhile the table name will maintain the "partlist####" but will be numerically random (ex. partlist452 or so on).

Again, thank you so much for your feedback.

[#10]

@Sunbed1060 

In any case user need to know filepath for such file and table name for the partlist. The latest could be check after the table is created

In the main file we created two named cells as parameters, let name them filepath and partlist (case sensitive when used in Power Query)

What user shall to do is copy/paste actual partlist table name here.

To get the table we use query like

let
    path = Excel.CurrentWorkbook(){[Name="filepath"]}[Content]{0}[Column1],
    name = Excel.CurrentWorkbook(){[Name="partlist"]}[Content]{0}[Column1],

    Source = Excel.Workbook(File.Contents(path), null, true),
    partlist_Table = Source{[Item=name,Kind="Table"]}[Data]
in
    partlist_Table

That's idea of the logic.