none
M-Language functions (use dynamic file location) RRS feed

  • Question

  • How do I tweak the function below in order to dynamically fetch a file's location?

    (currently, source is hard coded) - thanks!


    (CleanUpParticipants) =>
    
    let
        Source = Csv.Document(File.Contents("C:\Users\Participants_January2018.txt"),[Delimiter="	", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Participant", type text}, {"ID", Int64.Type}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Participant", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Participant.1", "Participant.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Participant.1", type text}, {"Participant.2", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Participant.1", "First Name"}})
    in
        #"Renamed Columns"


    Rafael Knuth


    Friday, February 23, 2018 2:57 PM

Answers

  • Say you have a parameter that stores the location you want to choose dynamically than this can be tweaked as follows

    (location) =>
    
    let
        Source = Csv.Document(File.Contents(location),[Delimiter="	", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Participant", type text}, {"ID", Int64.Type}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Participant", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Participant.1", "Participant.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Participant.1", type text}, {"Participant.2", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Participant.1", "First Name"}})
    in
        #"Renamed Columns"

    given that parameter location has value "C:\Users\Participants_January2018.txt" you should get the same result

     
    • Marked as answer by Rafael Knuth Monday, February 26, 2018 9:22 AM
    Friday, February 23, 2018 9:53 PM
  • Hey,

    Could you please explain a bit more about what needs to be dynamically calculated? is it the csv filepath in your source step? when you say dynamic, could you please specify what needs to be dynamic? does it need to reference another query or do you mean that it should be translated into a parameter so people can input their desired file path?

    I wouldn't recommend going the route of making your filepath a variable as there's a high chance that your query won't even refresh on the Power BI Service due to the static query analysis that the service does when try to perform a refresh. You could get away with it by using a Power Query Parameter and that might even be a good idea for when you're creating a pbit file.

    To change the data source is always better to go to the Data Source Settings Window --> select the source that you want to change and then click in the button below that says something among the lines of "Change Source..." as shown below

    • Marked as answer by Rafael Knuth Monday, February 26, 2018 9:22 AM
    Friday, February 23, 2018 3:47 PM
  • Hi Rafael,

    Igor's function does exactly that :-)

    But if I understand Miguel right, this method could lead to problems when refreshing in the service. To prevent this, you would instead pass the binary (file content) to the function (instead of the text of its path/connection). In that case you have to omit File.Contents(..) in the first step like this:

    (fileContent as binary) =>
    
    let
        Source = Csv.Document(fileContent,[Delimiter="     ", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Participant", type text}, {"ID", Int64.Type}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Participant", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Participant.1", "Participant.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Participant.1", type text}, {"Participant.2", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Participant.1", "First Name"}})
    in
        #"Renamed Columns"

    A function like this would easily work for imports via "From Folder" for example.

    Cheers, Imke


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!



    Saturday, February 24, 2018 1:53 PM
    Moderator
    • Marked as answer by Rafael Knuth Monday, February 26, 2018 9:22 AM
    Sunday, February 25, 2018 2:20 AM

All replies

  • Hey,

    Could you please explain a bit more about what needs to be dynamically calculated? is it the csv filepath in your source step? when you say dynamic, could you please specify what needs to be dynamic? does it need to reference another query or do you mean that it should be translated into a parameter so people can input their desired file path?

    I wouldn't recommend going the route of making your filepath a variable as there's a high chance that your query won't even refresh on the Power BI Service due to the static query analysis that the service does when try to perform a refresh. You could get away with it by using a Power Query Parameter and that might even be a good idea for when you're creating a pbit file.

    To change the data source is always better to go to the Data Source Settings Window --> select the source that you want to change and then click in the button below that says something among the lines of "Change Source..." as shown below

    • Marked as answer by Rafael Knuth Monday, February 26, 2018 9:22 AM
    Friday, February 23, 2018 3:47 PM
  • Say you have a parameter that stores the location you want to choose dynamically than this can be tweaked as follows

    (location) =>
    
    let
        Source = Csv.Document(File.Contents(location),[Delimiter="	", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Participant", type text}, {"ID", Int64.Type}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Participant", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Participant.1", "Participant.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Participant.1", type text}, {"Participant.2", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Participant.1", "First Name"}})
    in
        #"Renamed Columns"

    given that parameter location has value "C:\Users\Participants_January2018.txt" you should get the same result

     
    • Marked as answer by Rafael Knuth Monday, February 26, 2018 9:22 AM
    Friday, February 23, 2018 9:53 PM
  • Hi Miguel,

    thanks for the prompt response.

    I basically want to reuse functions to perform mundane tasks on identically structured CSV files that are coming in periodically. That being said, I want to enter the file location as a parameter when invoking the function, instead of hard coding the file path into the function itself and copying and pasting it whenever a new CSV file comes in. Hope that answers your question.


    Rafael Knuth


    • Edited by Rafael Knuth Saturday, February 24, 2018 12:40 PM
    Saturday, February 24, 2018 12:39 PM
  • Hi Rafael,

    Igor's function does exactly that :-)

    But if I understand Miguel right, this method could lead to problems when refreshing in the service. To prevent this, you would instead pass the binary (file content) to the function (instead of the text of its path/connection). In that case you have to omit File.Contents(..) in the first step like this:

    (fileContent as binary) =>
    
    let
        Source = Csv.Document(fileContent,[Delimiter="     ", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Participant", type text}, {"ID", Int64.Type}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Participant", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Participant.1", "Participant.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Participant.1", type text}, {"Participant.2", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Participant.1", "First Name"}})
    in
        #"Renamed Columns"

    A function like this would easily work for imports via "From Folder" for example.

    Cheers, Imke


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!



    Saturday, February 24, 2018 1:53 PM
    Moderator
  • cool, awesome, thanks Igor!

    Rafael Knuth

    Saturday, February 24, 2018 2:04 PM
  • my bad, now I see the whole picutre. thanks!

    Rafael Knuth

    Saturday, February 24, 2018 2:05 PM
    • Marked as answer by Rafael Knuth Monday, February 26, 2018 9:22 AM
    Sunday, February 25, 2018 2:20 AM