Power Query Add part of the file path to the data

[sabd80]

Hi,

I am loading 12 files from SharePoint into Power BI, with each file representing a specific period or month. Since this information is not present within the files, I extract it from the file path. To incorporate this data, I have created a new column called "Period."

= Table.AddColumn(#"Filtered Rows", "Custom", each Text.Middle( [Folder Path], 123, 11))

I want to add the "Period" column to each data file to label each row with the corresponding period.

Can you help?

[v-pgoloju]

Hi @sabd80,

Just a gentle reminder  has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @sabd80,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution  it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @sabd80,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[kushanNa]

Hi,

When you say '12 files,' are these Excel files that you are trying to combine? And once you combine them, should each row have a period assigned to it? Something like that? Any sample data or screenshot you can provide to show what you're trying to do here?

[Khushidesai0109]

Hiii @sabd80 

Which part do you need to include in new column??

[sabd80]

I need to include Periodic_01 from the folder path.

I already created the column Period which has that part from the path.

[Khushidesai0109]

Try this

= Table.AddColumn(#"Filtered Rows", "Period", each Text.AfterDelimiter([Folder Path], "/", {0, RelativePosition.FromEnd}), type text)

[sabd80]

it is returning blank

[Khushidesai0109]

Can you share me the 1-2 path for me to understand and give you proper response

[sabd80]

https://rsbsb.sht.com/sites/O365-SAPLInventoryModel/Shared Documents/General/01_Periodic_Loads/Current_Year/Actual/Periodic_01/

[Khushidesai0109]

= Table.AddColumn(#"Filtered Rows", "Period", each Text.AfterDelimiter([Folder Path], "/Actual/"), type text)

try this please

[sabd80]

@Khushidesai0109 , I know how to extract the part I want, what I need help with how to add this column to the data in each file.

[Khushidesai0109]

Steps to Add "Period" to Each File's Data in Power BI

1. Ensure the "Period" Column Exists Before Expanding Files

You are currently working with metadata (file details) before expanding the contents. That’s the right place to extract Period, so ensure this step happens before combining the files.

2. Expand the Binary Contents (CSV or Excel Files)

• Click the expand icon next to the Content/Binary column.
• Select the relevant columns from the data inside the files.

  3. Add the "Period" Column to the Expanded Data

  Once expanded, the extracted "Period" column should still be present from the earlier step. However, if it's missing after expansion, follow these steps:

  • In Power Query, locate the step where you expand the data.
  • Ensure "Period" is retained after expansion.
  • If it disappears, merge back the "Period" column by using a common key (such as Name or Folder Path).

    4. Use Table.AddColumn to Append "Period" to Each Row

    If the "Period" column is lost after expansion, explicitly add it using:

    = Table.AddColumn(ExpandedTable, "Period", each LookupTable[Period]{List.PositionOf(LookupTable[Name], [Name])}, type text)

    (This ensures "Period" from metadata is applied to the expanded table.)

    5. Load the Final Table into Power BI

    • Ensure the final table includes all file data with the "Period" column.
    • Click Close & Apply to load it into Power BI.

[sabd80]

I could not see the column Period after the expanssion, so I applied the code in point 4, but I get this error:

Expression.Error: The name 'LookupTable' wasn't recognized. Make sure it's spelled correctly.