如何在幂bi中将yyyymm(数字)转换为mmm-yyyy

Question

如何在power bi中将yyyymm(数字(转换为mmm-yyyy？

数据集：

YRMONTH ABCD  CBDA  BDAC
202101  1234  1234  1233
202102  1233  1233  1234
202103  1234  1234  1234
...

目标：

YRMONTH  ABCD  CBDA  BDAC
Jan-2021 1234  1234  1233
Fab-2021 1233  1233  1234
Mar-2021 1234  1234  1234
... 

并将其转为

Jan-2021  Feb-2021  Mar 2021 ... Till Endofyear
ABCD   1234      1233      1233
CBDA   1234      1233      1234
BDAC   1233      1234      1234
...

Answer 1

您可以获取年份和月份，将其转换为日期，然后将其格式化为MMM-yyyy。

代码的关键位是

each Date.ToText(#date(Number.IntegerDivide(_, 100), Number.Mod(_, 100), 1), "MMM-yyyy")

但这是完整的M代码，你可以粘贴到高级编辑器：

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjQwVNJRMjQyNkGhjJVidaDyRjARZMoEIW+MTT9QPhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YRMONTH = _t, ABCD = _t, CBDA = _t, BDAC = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"YRMONTH", Int64.Type}, {"ABCD", Int64.Type}, {"CBDA", Int64.Type}, {"BDAC", Int64.Type}}),
#"Convert Int to Date Text" = Table.TransformColumns(#"Changed Type", {{"YRMONTH", each Date.ToText(#date(Number.IntegerDivide(_, 100), Number.Mod(_, 100), 1), "MMM-yyyy"), type date}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Convert Int to Date Text", {"YRMONTH"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Columns", {{"YRMONTH", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Columns", {{"YRMONTH", type text}}, "en-US")[YRMONTH]), "YRMONTH", "Value")
in
#"Pivoted Column"