Forum Discussion

liuj3's avatar
liuj3
New Member
4 years ago
Solved

how to convert data Q1, Q2, Q3 Q4 to numeric in powerQuery Editor

Hi everyone, i have difficulties about converting BIS data Q1, Q2, Q3, Q4 to mm/dd/yy format. the following code is used for get data from BIS website. the the second screen shot is data i got. is there anyways to convert time period data to format like 3rd photos? 

 

 

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    You can issue following statement where Source should be replaced with your previous step

    Table.ReplaceValue(Source,each [#"Attribute:TIME_PERIOD"],each Date.EndOfMonth(#date(Number.From(Text.BeforeDelimiter([#"Attribute:TIME_PERIOD"],"-")),Number.From(Text.End([#"Attribute:TIME_PERIOD"],1))*3,1)),Replacer.ReplaceValue,{"Attribute:TIME_PERIOD"})

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ0N9cNNFGK1QGzLXQDDaFsCwPdQCMo29JSN9AYzDYyMDAGq4kFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Attribute:TIME_PERIOD" = _t]),
    Custom = Table.ReplaceValue(Source,each [#"Attribute:TIME_PERIOD"],each Date.EndOfMonth(#date(Number.From(Text.BeforeDelimiter([#"Attribute:TIME_PERIOD"],"-")),Number.From(Text.End([#"Attribute:TIME_PERIOD"],1))*3,1)),Replacer.ReplaceValue,{"Attribute:TIME_PERIOD"}),
    #"Changed Type" = Table.TransformColumnTypes(Custom,{{"Attribute:TIME_PERIOD", type date}})
in
    #"Changed Type"

4 Replies

  • jbwtp's avatar
    jbwtp
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi liuj3,

     

    The simpliest is the straightforward replacement.Something like this:

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ0N9cNNFGK1QGzLXQDDZHYRkhsYyQ2UH0sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Attribute: TIME_PERIOD" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute: TIME_PERIOD", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Q4","12-31",Replacer.ReplaceText,{"Attribute: TIME_PERIOD"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Q1","03-31",Replacer.ReplaceText,{"Attribute: TIME_PERIOD"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Q2","06-30",Replacer.ReplaceText,{"Attribute: TIME_PERIOD"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Q3","09-30",Replacer.ReplaceText,{"Attribute: TIME_PERIOD"}),
    #"Parsed Date" = Table.TransformColumns(#"Replaced Value3",{{"Attribute: TIME_PERIOD", each Date.From(DateTimeZone.From(_)), type date}})
in
    #"Parsed Date"

     

     

     

    Alternatively, you can create a table of dates to quarters. Spit the time period column to separate out Qx andthen merge using the dates to quarters table.

     

    Kind regards,

    John

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    4 years ago

    NewStep=Table.TransformColumns(Source{1}[Table]{0}[#"Namespace:"]{0}[Series]{0}[Obs],{"Attribute:TIME_PERIOD",each Date.AddDays(Date.AddQuarters(Date.From(Text.BeforeDelimiter(_,"-")&"-1-1"),Number.From(Text.AfterDelimiter(_,"Q"))),-1)})

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    You can issue following statement where Source should be replaced with your previous step

    Table.ReplaceValue(Source,each [#"Attribute:TIME_PERIOD"],each Date.EndOfMonth(#date(Number.From(Text.BeforeDelimiter([#"Attribute:TIME_PERIOD"],"-")),Number.From(Text.End([#"Attribute:TIME_PERIOD"],1))*3,1)),Replacer.ReplaceValue,{"Attribute:TIME_PERIOD"})

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ0N9cNNFGK1QGzLXQDDaFsCwPdQCMo29JSN9AYzDYyMDAGq4kFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Attribute:TIME_PERIOD" = _t]),
    Custom = Table.ReplaceValue(Source,each [#"Attribute:TIME_PERIOD"],each Date.EndOfMonth(#date(Number.From(Text.BeforeDelimiter([#"Attribute:TIME_PERIOD"],"-")),Number.From(Text.End([#"Attribute:TIME_PERIOD"],1))*3,1)),Replacer.ReplaceValue,{"Attribute:TIME_PERIOD"}),
    #"Changed Type" = Table.TransformColumnTypes(Custom,{{"Attribute:TIME_PERIOD", type date}})
in
    #"Changed Type"