Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
NumeritasMartin
Frequent Visitor

From Folder, filter by specific TABs, transform TABs from each workbook differently.

Good Afternoon,

 

I have an interesting scenario and wondering how others would solve it, I am currently looking at the route of doing each file separately but I am sure there is a better way.

 

Scenario is;

Multiple Excel source files with multiple TABs but only certain one's are needed, call them currency transaction TABs labelled as (EUR, USD, GBP etc.)

 

I am able to filter the source to those specific items from another table.

 

The TAB's in each workbook are slightl;y different, the date and reference column are swopped in one file casuing errors when the files are loaded together.

 

Ideally I'd like to

A) Get the data from one TAB and make some transformations, 'promote headers'; 'align columns' etc, apply that process to all the sel;ected TAB's in that workbook.

 

B) Do the same for the next workbook/s.

 

C) load the files together as per the usual get data from folder function.

 

D) Make any transformations I need to make on the whole dataset

 

 

Am I going to be stuck in doing each workbook individually?

 

Thanks for reading

 

Martin

 

3 REPLIES 3
smpa01
Super User
Super User

Hello @NumeritasMartin,

It is always difficult to answer without a sample data. However, I like to think that it can be achieved what you have in mind.

 I have assumed that you are looking for a batch process (for a cluster of transformation) per file. It can be done by writing s custom code and running that code on each file. For example, I have created a sample dataet as following

Capture.JPG

 and I want to run a following transformation

a) Unselect any tabs that are not USD/GBP - File level transformation

b) From each table pick up only the colums => Name/Value only - Tab level transformation

c) Promoted Headers - Tab level transformation

d) Finally produce an appened result from all the trsnformed tables from each of the tab with the name of the Tab in a separate column

Capture.JPG

(P as text)=>let
    Source = Excel.Workbook(File.Contents(P), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "Non-Currency")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Data"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each let
   Source=[Data],
   #"Transposed Table" = Table.Transpose(Source),
    #"Sorted Rows" = Table.Sort(#"Transposed Table",{{"Column1", Order.Ascending}}),
    #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([Column1] = "Value") or ([Column1] = "Name")),
    #"Transposed Table1" = Table.Transpose(#"Filtered Rows1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Value"}, {"Name.1", "Value"})
in
    #"Expanded Custom"

When you invoke this function please input the value of P as the path of the file. e.g. "C:\Users\Desktop\Test1\X.xlsx" but without the quoted marks on each end as this - C:\Users\Desktop\Test1\X.xlsx

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Thank you for the quick reply @smpa01 , your solution looks like it should work for me, I am trying to put it into application but I'm running into a little problem because I don't quite understand what steps happen when, the issue I am having is basically I have a top row to remove before the transposition step, but when I then try to filter the rows on my expected results I'm getting an error of value not found.

 

Transform PIC.PNG

 

I put a filter in so that I can pull the TAB names from a table which works fine, I plan to do the same with the 'Columns' too.

 

Thanks for your help

 

Martin

@NumeritasMartin  sory could not have come back to you earlier

 

Can you try this

(P as text)=>let
    //Connecting to file
    Source = Excel.Workbook(File.Contents(P), null, true),
    //Remove Unnecessary tabs, known tabs are known to the analyst
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "Non-Currency")),
    //Steps to initiate transormation steps in sequence in each table
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom", each let
   Source = [Data],
   // Remove Random row that resides at row#1 from each table
   #"Removed Top Rows" = Table.Skip(Source,1),
   // Transpose Table
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Sorted Rows" = Table.Sort(#"Transposed Table",{{"Column1", Order.Ascending}}),
    #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([Column1] = "Value") or ([Column1] = "Name")),
    #"Transposed Table1" = Table.Transpose(#"Filtered Rows1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Value", Int64.Type}})
in
    #"Changed Type"),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Name", "Value"}, {"Name", "Value"})
in
    #"Expanded Custom1"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.