cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GeekAlfPro
Advocate I
Advocate I

File name startwith as parameter

Hello,

 

i built a few queries with power query and i want to pass the filenames as parameters.

often, the files start with the same string and end with dates or numbers.

 

i managed to have the filenames, but i'm stuck when i want to transform it in parameters.

For example the following steps : 

 

let
    Source = Folder.Files(Dossier_Données),
    #"Colonne conditionnelle ajoutée" = Table.AddColumn(Source, "NomFichier", each if 
Text.StartsWith([Name], "L083_-_Liste_absences_toutes_categories_par_salarié_sur_une_période") then [Name] 
else null),
    #"Colonnes permutées" = Table.ReorderColumns(#"Colonne conditionnelle ajoutée",{"Content", "Name", "Extension", "NomFichier", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"Lignes filtrées" = Table.SelectRows(#"Colonnes permutées", each ([NomFichier] <> null)),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    NomFichier = #"Colonnes supprimées"{0}[NomFichier]
in
    NomFichier

 

is there a way to achieve this ?

 

thanks

1 ACCEPTED SOLUTION
smpa01
Memorable Member
Memorable Member

	let
    fx=(p as text)=> let
    Source = Folder.Files(Dossier_Données),
    #"Colonne conditionnelle ajoutée" = Table.AddColumn(Source, "NomFichier", each if 
Text.StartsWith([Name], p) then [Name] 
else null),
    #"Colonnes permutées" = Table.ReorderColumns(#"Colonne conditionnelle ajoutée",{"Content", "Name", "Extension", "NomFichier", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"Lignes filtrées" = Table.SelectRows(#"Colonnes permutées", each ([NomFichier] <> null)),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    NomFichier = #"Colonnes supprimées"{0}[NomFichier]
in
    NomFichier
    in 
    fx

New Animated Dashboard: Sales Calendar


View solution in original post

3 REPLIES 3
smpa01
Memorable Member
Memorable Member

	let
    fx=(p as text)=> let
    Source = Folder.Files(Dossier_Données),
    #"Colonne conditionnelle ajoutée" = Table.AddColumn(Source, "NomFichier", each if 
Text.StartsWith([Name], p) then [Name] 
else null),
    #"Colonnes permutées" = Table.ReorderColumns(#"Colonne conditionnelle ajoutée",{"Content", "Name", "Extension", "NomFichier", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"Lignes filtrées" = Table.SelectRows(#"Colonnes permutées", each ([NomFichier] <> null)),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    NomFichier = #"Colonnes supprimées"{0}[NomFichier]
in
    NomFichier
    in 
    fx

New Animated Dashboard: Sales Calendar


View solution in original post

Hello @smpa01 

it works, thanks.

just a question, i had the "rebuild this data combination" error in all nested queries in my project, is it a normal behaviour of Power Query ?

 

i found a workaround here

I am sorry to hear that you are getting that error. Power query does generate that weird error. I also had that error at some point. I also refereed to the same URL as you did. But don't remeber if that solved my trouble.

 

Do you mind creating a seperate thread for that issue in particular with all the detailed code and snapshot of the query step which generates this bug.

 

I will try to solve that but if dor some reason I can't, someone else will for sure.


New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors