Extract Date Quarter String and Year int columns IN POWERQUERY

[Anonymous]

Hi all,

I have 2 fct tables on quarter  level and I have to connect these 2 fct tables to my date table which is on day level. (the dim date is day level because I have more 3 fact tables on day level connected to it)

How do I extract the first day of the quarter from the columns?

The table has the columns Quarter and Year as String and Int, I already tried to convert to date format and when I do that the Year gets all messed up, the 2020 becomes like 1950.

Quarter	Year
Q2	2020
Q4	2020
Q1	2019

and I need the below: (First day of the quarter)

Quarter	Year	Date Column
Q2	2020	1/04/2020
Q4	2020	1/09/2020
Q1	2019	1/01/2020

Thank you

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Inserted Text After Delimiter" = Table.AddColumn(Source, "Text After Delimiter", each Text.AfterDelimiter([Quarter], "Q"), type number),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Text After Delimiter",{{"Text After Delimiter", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [Text After Delimiter] = 1 then 1 else if [Text After Delimiter] = 2 then 4 else if [Text After Delimiter] = 3 then 7 else 10, type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each "1/"&Number.ToText([Custom])&"/"&Number.ToText([Year])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Text After Delimiter", "Custom"})
in
    #"Removed Columns"

Hope this helps.

[selimovd]

Hey @Anonymous ,

you can just check for the quarter and then return the corresponding month.
Try the following calculated column:

Date Column =
VAR vCalcMonth =
    SWITCH(
        'myTable'[Quarter],
        "Q1", 1,
        "Q2", 4,
        "Q3", 7,
        "Q4", 10
    )
RETURN
    DATE( 'myTable'[Year], vCalcMonth, 1 )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi