Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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"
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
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.
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
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.
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
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.
i mean i want date duration between jan 1996 to jan 2018 ..
and currently i saw only 2 years..
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"
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"
what anout others ?
You will have to show me some example data so i know what i should build.
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 ..
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |