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

[liuj3]

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? 

[liuj3]

thank you , problem is solved 

[wdx223_Daniel]

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 · Accepted solution]

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"

[jbwtp]

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