Power Query - Parameter

Power Query
1

Hi everyone,

Hoping to get some help in Power query - Dataflows

I am connecting to an Excel file in Sharepoint with multiple tabs.

The current configuration of Transformation steps allows the tab name to be always the same name to pick the correct tab as shown below screenshot Item = Data May 2024 ( Excel tab name)

Is there a way to make it dynamic/wildcard like item = Data%

I cannot get a resource to understand whether it is possible.

Can anyone have a similar situation? Can you advise if this is possible?

I appreciate any help or advice on this.

Dataflow_Power_query_Parameter
Dataflow_Power_query_Parameter1274Ă—769 165 KB

2

Hi @nadeena_mini,

Switch from key-match lookup to select rows, try this:

let
    Source = Excel.Workbook(#"Paramter (2)", null, true),
    Navigation = Table.SelectRows(Source, each Text.StartsWith( [Item], "Data") and [Kind] = "Sheet" ){0}?[Data]?
in 
    Navigation

I hope this is helpful

3

Thanks so much Melisa, let me give this a try. Appreciate your help

4

Thanks @Melissa this worked yay!!

Appreciate your time and help with this. Is there any EDNA videos that you recommend to understand this concepts better please suggest

5

Hi @nadeena_mini,

Here are some of the courses with EDNA platform:

image
image907Ă—958 114 KB

Using the Datamentor “Code Explainer” built within EDNA platform.

this what comes up:

# Code Explanation

The provided code snippet is written in M Language, commonly used in Power Query within Excel and Power BI for data transformation tasks.

## Code Breakdown

### 1. `Source` Step

```m
Source = Excel.Workbook(#"Parameter (2)", null, true)
  • Purpose: Reads data from an Excel workbook.
  • Function: Excel.Workbook function is used to import data.
    • #"Parameter (2)": Refers to the specific Excel file or parameter that contains the workbook data.
    • null: This indicates that no particular encoding is specified.
    • true: Indicates that the function should try to automatically detect the data structure.

2. Navigation Step

Navigation = Table.SelectRows(Source, each Text.StartsWith( [Item], "Data") and [Kind] = "Sheet" ){0}?[Data]?
  • Purpose: Filters and extracts a specific sheet containing data.
  • Function:
    • Table.SelectRows: Filters rows in the imported workbook’s data table.
    • each Text.StartsWith([Item], "Data") and [Kind] = "Sheet": Condition to select rows where the Item column starts with “Data” and the Kind column is “Sheet”.
    • {0}: Selects the first row from the filtered result.
    • ?[Data]?: Accesses the Data column from the selected row, returning the sheet’s data.

3. in Navigation Step

in 
    Navigation
  • Purpose: Specifies the final output of the query.
  • Function: The transformed data stored in Navigation is returned as the final result of the query.

Summary

  • Source: Imports the workbook data.
  • Navigation: Filters for a specific sheet named “Data” and returns its contents.
  • Final Output: The contents of the targeted sheet.

This code effectively imports an Excel file, identifies a specific sheet, and extracts the data from that sheet for further processing or analysis.

Additional Insights

  • Common Functions:
    • Excel.Workbook is frequently used for data loading in Power Query.
    • Table.SelectRows is essential for filtering table data based on conditions.

Alternative Example

Here’s an example where a different sheet is selected:

Source = Excel.Workbook(File.Contents("PathToWorkbook.xlsx"), null, true),
Sheet2Data = Table.SelectRows(Source, each [Item] = "Sheet2" and [Kind] = "Sheet"){0}?[Data]?
in 
    Sheet2Data
  • In this example, it selects the sheet explicitly named “Sheet2” instead of using a dynamic condition.

For a broader understanding of data transformation in M Language and Power Query, consider exploring resources on the Enterprise DNA Platform.

I hope this helps.
Keith

1 Like
6

Thank you!!