Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Yubo
Helper I
Helper I

How to Keep one column and Union other 5 column into 2 columns in one table?

Hello everyone,

is it possible to convert the original table with 6 columns into 3 columns?

 

Feb27.png

 

 

 

 

 

 

 

 

 

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 🙂

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

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]

22.PNG

 

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"

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

6 REPLIES 6
v-frfei-msft
Community Support
Community Support

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]

22.PNG

 

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"

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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,

🙂

Greg_Deckler
Super User
Super User

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"

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi 

 From the code, I got it as below, Need A1,A2,B1,B2 move to Atttibute column. is ther any ways ? Thanks you !!

Feb29.png

 

 

 

Hi @Yubo ,

 

That is not about DAX. We can use the M code in power query as the pictures below.

 

Capture.PNG

2.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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!

Feb28.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.