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 everyone, if I have 1 column that contains course codes e.g. F2001 where 20 means 2020 and 01 means first term but I want to add a corresponding year in a new column next to it so what should I do? I have a number of course codes in this column F2101,F2102,F2001,F2002,F1801,F1802,F1901,F1902 (so the first 2 digit refers to the year and last two is the term so Term 1 or Term 2). The year column is not in my table but I can manually add it (which is super quick) but I want to see how it could be done in power query. I want the year column to contain Term 1 2018, Term 2 2018..or just the year. Thankyou!
Solved! Go to Solution.
Paste this into the advanced editor of a blank query...
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText("i45WcjMyNDDUAZFGQNIAzDYAsQ0tQGwgCWJbgtmWBkZKsbEA", BinaryEncoding.Base64),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Column1 = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(
#"Changed Type",
"Column1",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{
"Column1.1",
"Column1.2",
"Column1.3",
"Column1.4",
"Column1.5",
"Column1.6",
"Column1.7",
"Column1.8"
}
),
#"Changed Type1" = Table.TransformColumnTypes(
#"Split Column by Delimiter",
{
{"Column1.1", type text},
{"Column1.2", type text},
{"Column1.3", type text},
{"Column1.4", type text},
{"Column1.5", type text},
{"Column1.6", type text},
{"Column1.7", type text},
{"Column1.8", type text}
}
),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns", {"Attribute"}),
#"Added Custom Column" = Table.AddColumn(
#"Removed Columns",
"Custom",
each Text.Combine({"Term ", Text.Middle([Value], 4), " 20", Text.Middle([Value], 1, 2)}),
type text
)
in
#"Added Custom Column"
Let me know if you need more assistance.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Paste this into the advanced editor of a blank query...
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText("i45WcjMyNDDUAZFGQNIAzDYAsQ0tQGwgCWJbgtmWBkZKsbEA", BinaryEncoding.Base64),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Column1 = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(
#"Changed Type",
"Column1",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{
"Column1.1",
"Column1.2",
"Column1.3",
"Column1.4",
"Column1.5",
"Column1.6",
"Column1.7",
"Column1.8"
}
),
#"Changed Type1" = Table.TransformColumnTypes(
#"Split Column by Delimiter",
{
{"Column1.1", type text},
{"Column1.2", type text},
{"Column1.3", type text},
{"Column1.4", type text},
{"Column1.5", type text},
{"Column1.6", type text},
{"Column1.7", type text},
{"Column1.8", type text}
}
),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns", {"Attribute"}),
#"Added Custom Column" = Table.AddColumn(
#"Removed Columns",
"Custom",
each Text.Combine({"Term ", Text.Middle([Value], 4), " 20", Text.Middle([Value], 1, 2)}),
type text
)
in
#"Added Custom Column"
Let me know if you need more assistance.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Hi @edithees ,
@KNP has given you the solution for your request. However, I would strongly recommend splitting your terms and years into separate columns. This will provide far more reporting flexibility against both term and year independently.
Adding onto the end of @KNP 's code, you would create these separately as new custom columns:
// Year
Text.Combine({"20", Text.Middle([Value], 1, 2)})
// Term
Text.Combine({"Term ", Text.Middle([Value], 4)})
Just my tuppence worth 🙂
Pete
Proud to be a Datanaut!
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.