Concatenate Text with Week Number column

[RahulPBI]

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

[AlB · Accepted solution]

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 

[AlB · Accepted solution]

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 

[RahulPBI]

@AlB 

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

[AlB · Accepted solution]

@RahulPBI 

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 

[RahulPBI]

Thank you 

Very much appreciated