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.
Hello everyone,
is it possible to convert the original table with 6 columns into 3 columns?
requests:
1. To keep Info column
2. To union first 5 cloumns into 2 columns (in blue)
3. Make final table has 3 columns in one taable.
Many thanks 🙂
Solved! Go to Solution.
Hi @Yubo ,
Please insert two custom columns as below.
if [Value] is date then [Value] else null
if [Value] is date then [Attribute] else [Attribute] & [Value]
M code for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lFyNAQSSHwnNH6UoVKsDkiHEVyHEVgFnO+Exo8yUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date_S = _t, #"Type1 " = _t, #"Date 2" = _t, Type2 = _t, #"Date 3" = _t, Info = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date_S", type date}, {"Type1 ", type text}, {"Date 2", type date}, {"Type2", type text}, {"Date 3", type date}, {"Info", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Date 2", "Date 3"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Info"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Value] is date then [Value] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Value] is date then [Attribute] else [Attribute] & [Value]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Info", "Attribute", "Value", "Custom.1", "Custom"})
in
#"Reordered Columns"
Hi @Yubo ,
Please insert two custom columns as below.
if [Value] is date then [Value] else null
if [Value] is date then [Attribute] else [Attribute] & [Value]
M code for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lFyNAQSSHwnNH6UoVKsDkiHEVyHEVgFnO+Exo8yUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date_S = _t, #"Type1 " = _t, #"Date 2" = _t, Type2 = _t, #"Date 3" = _t, Info = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date_S", type date}, {"Type1 ", type text}, {"Date 2", type date}, {"Type2", type text}, {"Date 3", type date}, {"Info", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Date 2", "Date 3"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Info"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Value] is date then [Value] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Value] is date then [Attribute] else [Attribute] & [Value]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Info", "Attribute", "Value", "Custom.1", "Custom"})
in
#"Reordered Columns"
Hi CST,
Thank you very much for your tip, I am gald not only fixed my issue, also give me more ideas about Power BI.
I appreciate it.
Best Regards,
🙂
So like this?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lFyNAQSSHwnNH6UoVKsDkiHEVyHEVgFnO+Exo8yUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date_S = _t, #"Type1 " = _t, #"Date 2" = _t, Type2 = _t, #"Date 3" = _t, Info = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date_S", type date}, {"Type1 ", type text}, {"Date 2", type date}, {"Type2", type text}, {"Date 3", type date}, {"Info", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Date 2", "Date 3"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Info"}, "Attribute", "Value")
in
#"Unpivoted Columns"
Hi Greg,
From the code, I got it as below, Need A1,A2,B1,B2 move to Atttibute column. is ther any ways ? Thanks you !!
Hi @Yubo ,
That is not about DAX. We can use the M code in power query as the pictures below.
Hi Frank,
I know this, but the difficulty part is the "Value" column should only contains "Date", the "type " should be in "Attribute" cloumn, like below:
Many Thanks!
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 |
---|---|
109 | |
99 | |
75 | |
63 | |
62 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
66 |