Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Format Time From YouTube Output

Hi all,   I have a set of YouTube data where I'm trying to format the duration column to something usable. YouTube exports the duration values out differently, dependent on if the video is hours, m...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi Anonymous

     

    Do the three formats exist in three columns?

    I test with the following dataset.

     

    When I import it to Power BI desktop and edit it in Query Editor, it shows as below.

     

     

    In Advanced Editor, edit code like this

     

    let

        Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\6\6.4\Format Time From YouTube Output.xlsx"), null, true),

        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

        #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Hours: ", type duration}, {"Minutes", type duration}, {"Seconds", type duration}}),

        #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Hours: ", type text}}, "en-US"), "Hours: ", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Hours: .1", "Hours: .2"}),

        #"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Minutes", type text}}, "en-US"), "Minutes", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Minutes.1", "Minutes.2"}),

        #"Split Column by Delimiter2" = Table.SplitColumn(Table.TransformColumnTypes(#"Split Column by Delimiter1", {{"Seconds", type text}}, "en-US"), "Seconds", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Seconds.1", "Seconds.2"}),

        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Hours: .1", type text}, {"Hours: .2", type text}, {"Minutes.1", type text}, {"Minutes.2", type text}, {"Seconds.1", type text}, {"Seconds.2", type text}}),

        #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Hours: .2", "Minutes.2", "Seconds.2"})

    in

    #"Removed Columns"

     

     

    Finally, I get this dataset

     

    Best Regards

    Maggie

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

Hi Anonymous

 

Do the three formats exist in three columns?

I test with the following dataset.

 

When I import it to Power BI desktop and edit it in Query Editor, it shows as below.

 

 

In Advanced Editor, edit code like this

 

let

    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\6\6.4\Format Time From YouTube Output.xlsx"), null, true),

    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Hours: ", type duration}, {"Minutes", type duration}, {"Seconds", type duration}}),

    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Hours: ", type text}}, "en-US"), "Hours: ", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Hours: .1", "Hours: .2"}),

    #"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Minutes", type text}}, "en-US"), "Minutes", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Minutes.1", "Minutes.2"}),

    #"Split Column by Delimiter2" = Table.SplitColumn(Table.TransformColumnTypes(#"Split Column by Delimiter1", {{"Seconds", type text}}, "en-US"), "Seconds", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Seconds.1", "Seconds.2"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Hours: .1", type text}, {"Hours: .2", type text}, {"Minutes.1", type text}, {"Minutes.2", type text}, {"Seconds.1", type text}, {"Seconds.2", type text}}),

    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Hours: .2", "Minutes.2", "Seconds.2"})

in

#"Removed Columns"

 

 

Finally, I get this dataset

 

Best Regards

Maggie

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi there,

     

    Thanks for your reply! I actually went about it in another way. I had to create three columns - one for each type of length - i.e. if the video is seconds, minutes or hours long.

     

    DAX as follows:

     

    Seconds: 

    ConvertSecs = IF(OR(LEN(YouTubeMainData[duration - Copy])=2,LEN(YouTubeMainData[duration - Copy])=3),"00:00:"&SUBSTITUTE(YouTubeMainData[duration - Copy],"S","")+0)

     

    Minutes: 

    ConvertMins = if(FIND("M",YouTubeMainData[duration - Copy],,BLANK())=2,"00:0"&SUBSTITUTE(SUBSTITUTE(YouTubeMainData[duration - Copy],"M",":"),"S",""))+0

     

    Hours: 

    ConvertHours = if(FIND("H",YouTubeMainData[duration - Copy],,BLANK())=2,"0"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(YouTubeMainData[duration - Copy],"H",":"),"M",":"),"S",""))+0

     

    And then a final column to bring it all together:

     

    Duration_Calc = YouTubeMainData[ConvertSecs]+YouTubeMainData[ConvertMins]+YouTubeMainData[ConvertHours]

     

    Thanks for your assistance anyhow!

     

    Sam