cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
themistoklis New Contributor
New Contributor

Convert a function to Individual Steps in PowerBI

Hello All,

 

I have a function that i want to convert it to individual steps on PowerBI and that's because i cannot schedule it to run automatically on the online PowerBI service.

 

I have managed to convert all the individual steps but im having issues on one of them.

 

The function is the following:

 

= (MkDailyCallsForAllSourceFile_template02 as any) => let
// use the template parameters 1 or 2 to check behaviour for each template
        Source = Csv.Document(File.Contents(MkDailyCallsForAllSourceFile_template02),[Delimiter=",", Columns=11, Encoding=1253, QuoteStyle=QuoteStyle.Csv]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
    // The date row has been shifted between the templates
        #"Isolate From row" = Table.SelectRows(#"Changed Type", each Text.Contains([Column1], "From ")),
    // Different text before the date
        ExtractedDate = fExtract3cxDate(#"Isolate From row"{0}[Column1], "From ", " To "),
    ReturnToTable = #"Changed Type",

    #"Removed Top Rows" = Table.Skip(ReturnToTable,6),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column3", "InAnswered"}, {"Column4", "InNonAnswered"}, {"Column5", "OutAnswered"}, {"Column6", "OutNonAnswered"}, {"Column7", "TotalAnswered"}, {"Column8", "TotalNonAnswered"}, {"Column1", "User"}}),
// Locate CallDuration
    LocateCallDuration = if #"Renamed Columns"{0}[Column9] = "" then Table.RenameColumns(#"Renamed Columns", {{"Column9", "ColumnX"}, {"Column10", "Call Duration"}}) else Table.RenameColumns(#"Renamed Columns", {{"Column9", "Call Duration"}, {"Column10", "ColumnX"}}),
    #"Removed Columns" = Table.RemoveColumns(LocateCallDuration,{"ColumnX", "Column11", "Column2"}) , 
in
    #"Call Date to Date"

 

The issue im having is that at the end of the process all the csv files that I load have the same date.

 

 

Any help on how to add the date for each CSV file will be greatly appreciated

 

Thanks

 

 

 

 

 

 

3 REPLIES 3
Community Support Team
Community Support Team

Re: Convert a function to Individual Steps in PowerBI

Hi @themistoklis,

 

Can you share the source file? Though I have the pbix file, I still can't see anything in the Query Editor. 

1. What's the details of "fExtract3cxDate"?

2." #"Isolate From row"{0}[Column1]" seems a single value, which leads to having the same date. What's the expected value?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
themistoklis New Contributor
New Contributor

Re: Convert a function to Individual Steps in PowerBI

@v-jiascu-msft

Please see attached link with source files as well.

The fExtract3cxDate gets the date from each csv file. You will see them in the attached file.

 

The following code actually has subfunctions within a function. So the initial function is the change type and the subfunctions are the Isolate from row and Extracted Date . The dificulty is to recreate the subfunctions.

So if you load the attached source files instead of having 3 different dates for each CSV file in the final dataset .. it has only one date

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
    // The date row has been shifted between the templates
        #"Isolate From row" = Table.SelectRows(#"Changed Type", each Text.Contains([Column1], "From ")),
    // Different text before the date
        ExtractedDate = fExtract3cxDate(#"Isolate From row"{0}[Column1], "From ", " To "),
    ReturnToTable = #"Changed Type"

 

themistoklis New Contributor
New Contributor

Re: Convert a function to Individual Steps in PowerBI

@v-jiascu-msft

 

On the first message i have edited the link and now it also has the source files.

The fExtract3cxDate extracts the date from the CSV files.

 

I think the problem is that subfunctions that i used within a function.

More specifically i have the subfunctions Isolate From row and the ExtractedDate under the function called Changed Type.

 

If you run the workspace with the source files you will notice that the same date is assigned to the whole dataset which is wrong.

The dataset needs to have 3 separate dates .. same as the number of files....

So a separate date for each table from CSV files

 

I hope this makes sense?