Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |