Quarter of year power bi

[Bakhtawar]

Hi all 

i am trying to get quarter from date .. 

i tried this query 

let
    Source = "",
    Custom1 = Source,
    Custom2 = Calendar2,
    Custom3 = let
    Source = List.Dates(#date(1996, 1, 1), 500, #duration(1, 0, 0, 0)),#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),StartDate = #date(2016, 1, 1),
    Today = DateTime.Date(DateTime.LocalNow()),
    Length = Duration.Days(Today - StartDate),
    Custom1 = #"Changed Type",
    #"Inserted Year" = Table.AddColumn(Custom1, "Fin Year", each Date.Year([Date]+#duration(184,0,0,0)), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Inserted Quarter of Year"= Table.AddColumn(#"Inserted Quarter of Year","Quarter of Year",each Date.QuarterOfYear([Date]),Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Day Name", "Fin Month", each if Date.Month([Date]) >=7 then Date.Month([Date])-6 else Date.Month([Date])+6  , Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "Day of Week", each Date.DayOfWeek([Date])+1, Int64.Type),
    #"Inserted First Characters" = Table.AddColumn(#"Inserted Day of Week", "MMM", each Text.Start([Month Name], 3), type text),
    #"Inserted First Characters1" = Table.AddColumn(#"Inserted First Characters", "DDD", each Text.Start([Day Name], 3), type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted First Characters1",{"Date", "Fin Year", "Month Name", "MMM", "Fin Month", "Day Name", "DDD", "Day of Week"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "YYMM", each ([Fin Year]-2000)*100 + [Fin Month]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"YYMM", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "MonthID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"MonthID", Int64.Type}})

in
    #"Changed Type2"
in
    Custom3

quarter line from above code

#"Inserted Quarter of Year"= Table.AddColumn(#"Inserted Quarter of Year","Quarter of Year",each Date.QuarterOfYear([Date]),Int64.Type),

when i tried this shows an error 

Expression.Error: The name 'Inserted Quarter of Year' wasn't recognized. Make sure it's spelled correctly.

and also there is probelm when  i rename "Fin Month " to only "Month" this shows also an error 

how to resolve this 

[tex628]

The code you posted is incorrect, use this:

let
    Source = List.Dates(#date(1996, 1, 1), 500, #duration(1, 0, 0, 0)),#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),StartDate = #date(2016, 1, 1),
    Today = DateTime.Date(DateTime.LocalNow()),
    Length = Duration.Days(Today - StartDate),
    Custom1 = #"Changed Type",
    #"Inserted Year" = Table.AddColumn(Custom1, "Fin Year", each Date.Year([Date]+#duration(184,0,0,0)), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Inserted Quarter of Year"= Table.AddColumn(#"Inserted Day Name","Quarter of Year",each Date.QuarterOfYear([Date]),Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Quarter of Year", "Fin Month", each if Date.Month([Date]) >=7 then Date.Month([Date])-6 else Date.Month([Date])+6  , Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "Day of Week", each Date.DayOfWeek([Date])+1, Int64.Type),
    #"Inserted First Characters" = Table.AddColumn(#"Inserted Day of Week", "MMM", each Text.Start([Month Name], 3), type text),
    #"Inserted First Characters1" = Table.AddColumn(#"Inserted First Characters", "DDD", each Text.Start([Day Name], 3), type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted First Characters1",{"Date", "Fin Year", "Month Name", "MMM", "Fin Month", "Day Name", "DDD", "Day of Week"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "YYMM", each ([Fin Year]-2000)*100 + [Fin Month]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"YYMM", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "MonthID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"MonthID", Int64.Type}})

in
    #"Changed Type2"

[Bakhtawar]

how this is incorrect will you please tell me 

and one more thing if i rename "Fin Year" to just "Year" this shows also an error 

i searched from google and paste it but i did not understand these 2 lines 

 #"Added Custom" = Table.AddColumn(#"Reordered Columns", "YYMM", each ([Fin Year]-2000)*100 + [Fin Month]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"YYMM", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "MonthID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"MonthID", Int64.Type}})

this error occured when i rename "fin year" to just "year"

Expression.Error: The column 'Fin Year' of the table wasn't found.
Details:
Fin Year

@tex628 

[tex628]

Everything is wrong.

This just doesnt do anything, custom2 is referencing something outside of the query.

let
    Source = "",
    Custom1 = Source,
    Custom2 = Calendar2,
    Custom3 = let

 
This order is completly wrong and results in returning a list of 1996 dates. Not the intended dates between 2016 and now.

Source = List.Dates(#date(1996, 1, 1), 500, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source,Splitter.SplitByNothing(),null,null,ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
StartDate = #date(2016, 1, 1),
Today = DateTime.Date(DateTime.LocalNow()),
Length = Duration.Days(Today - StartDate),
Custom1 = #"Changed Type",

The quarter line is referencing itself when it should be referencing the previous line.

#"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Quarter of Year"= Table.AddColumn(#"Inserted Quarter of Year","Quarter of Year",each Date.QuarterOfYear([Date]),Int64.Type),

Recreate the entire calendar, it's not worth it trying to get this one to work. 

[Bakhtawar]

why you start from 2016 ?

StartDate = #date(2016, 1, 1),

and you did not answer my last lines.. 

#"Added Custom" = Table.AddColumn(#"Reordered Columns", "YYMM", each ([Fin Year]-2000)*100 + [Fin Month]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"YYMM", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "MonthID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"MonthID", Int64.Type}})

 and also did not answer when i change "Fin Year" to "Year" then why this shows an error 

@tex628 

[tex628]

The startdate of 2016 is from the code you posted:


This code produces two custom columns. One is a combination of year and month. One is a unique monthID. 

#"Added Custom" = Table.AddColumn(#"Reordered Columns", "YYMM", each ([Fin Year]-2000)*100 + [Fin Month]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"YYMM", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "MonthID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"MonthID", Int64.Type}})

 
Changing "Fin year" to "year" produces an error because fin year is referenced in the YYMM column. This means that you cant change fin year without changing YYMM.

[Bakhtawar]

i see. .the date section .. i wants date between 1996 till 2018 how i configure accordind to this . 

i check there is only 2 years.. 1996 and 1997

as i already told you that i also copy paste this code from internet .. so i dont know why this 2016 occured

@tex628 

[tex628]

The code that you copied is broken. You shouldn't use it. 

Which different fields do you need in your calendar? 

I'll make you one.

[Bakhtawar]

i mean i want date duration between jan 1996 to jan 2018 .. 

and currently i saw only 2 years..

 

@tex628 

[tex628]

It's because the code is bad. Use this:

let
    Source = List.Dates( Date.FromText("1996-01-01") , Number.From(Date.FromText("2018-01-01")) - Number.From(Date.FromText("1996-01-01")) +1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Column1])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month", each Date.Month([Column1])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Quarter", each Date.QuarterOfYear([Column1]))
in
    #"Added Custom2"

[Bakhtawar]

hey 

thanku is this possible to get all this 

@tex628 

[tex628 · Accepted solution]

Change it to this:

let
    Source = List.Dates( Date.FromText("1996-01-01") , Number.From(Date.FromText("2018-01-01")) - Number.From(Date.FromText("1996-01-01")) +1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month", each Date.Month([Date])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Quarter", each Date.QuarterOfYear([Date])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Month Name", each Date.MonthName([Date])),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Day Name", each Date.DayOfWeekName([Date]))
in
    #"Added Custom4"

[Bakhtawar]

what anout others ?

[tex628]

You will have to show me some example data so i know what i should build.

[Bakhtawar]

this is the link file 

https://www.dropbox.com/s/tdsf0xhogr4opgr/test_fle.pbix?dl=0 

in this i want to link datekey which is in calendar table with orderfact table.. howevery there is no datekey in order fact table so i want to add this date key in fact table and link both tables like this 

check calendar table and sales fact table.. and also i want all fields which is in calendar table .. 

@tex628