Forum Discussion
Concatenate Text with Week Number column
Hi Experts
I am trying to concatenate text with the a week number column but I am getting an error. I've tried two diffrent options and none work
= &"CONCATENATE("WK-",Calendar[Week])
= &"WK-"&[Week]
Can someone please help me
Regards
Rahul
Hi RahulPBI
Is what you are showing the code for a custom column in the query editor?? If so, your second option is close:
= "WK-"&[Week]
assuming of course that "Week" is the name of the column you already have and it has the week number
If this is not it share a sample of your data. This is very important to get you question answered
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Just update the last step. The error message is saying quite clearly what the problem is: "We cannot apply operator & to types Text and Number" [Week] is of type numer so you have to convert it to text before applying the & operator:
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Week1", each "WK-"& Text.From( [Week] ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
4 Replies
Hi RahulPBI
Is what you are showing the code for a custom column in the query editor?? If so, your second option is close:
= "WK-"&[Week]
assuming of course that "Week" is the name of the column you already have and it has the week number
If this is not it share a sample of your data. This is very important to get you question answered
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thanks for your reply
Please see below what I have done so far
let
EndFiscalYearMonth = 6, //set this as the last month number of your fiscal year : June = 6, July =7 etc
StartDate= #date(2019, 1, 1), // Change start date #date(yyyy,m,d)
EndDate = #date(2020, 12, 31), // Could change to DateTime.LocalNow() if you want to always show up to the current date
/* Comment out the above StartDate and EndDate using // if you want to use a dynamic start and end date based on other query/table
You will need to change "Sales" and "Invoice Date" in 2 lines below and then remove the //
*/
//TableName = Sales,
//DateColumnName = "Invoice Date",
//StartDate = Record.Field ( Table.Min(TableName,DateColumnName) ,DateColumnName),
//EndDate = Record.Field(Table.Max(TableName,DateColumnName),DateColumnName),
DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),#"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Calendar Year", each Date.Year([Date]), type number),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Year",{{"Calendar Year", Int64.Type}}),
#"Inserted Month Number" = Table.AddColumn(#"Changed Type1", "Month Number", each Date.Month([Date]), type number),
#"Changed Type2" = Table.TransformColumnTypes(#"Inserted Month Number",{{"Month Number", Int64.Type}}),
#"Long Month Name" = Table.AddColumn(#"Changed Type2", "Month Long", each Date.MonthName([Date]), type text),
#"Inserted Week of Year" = Table.AddColumn(#"Long Month Name", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Week of Year",{{"Week of Year", "Week"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Week1", each "WK-"&[Week])
in
#"Added Custom"Regards
Rahul
Just update the last step. The error message is saying quite clearly what the problem is: "We cannot apply operator & to types Text and Number" [Week] is of type numer so you have to convert it to text before applying the & operator:
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Week1", each "WK-"& Text.From( [Week] ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
