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
Anonymous
Not applicable

New Table With Union and another Column

Hello Gurus - Hoping for some help as I am unable to find my exact issue.  Thank you in advance!

 

I have a table that contains the following columns I am interested in;

Internal ID

Label

Label1

Label2

Label3

Label4

Label5

 

I am looking to get the Labels unioned into 1 columns with the proper correspending IternalIDs

 

I can union the Labels

All_Archs = Distinct (
UNION(
SELECTCOLUMNS('pub dv_IndexedLabel', "Label", 'pub dv_IndexedLabel'[Label]),
SELECTCOLUMNS('pub dv_IndexedLabel', "Label", 'pub dv_IndexedLabel'[Label2]),
SELECTCOLUMNS('pub dv_IndexedLabel', "Label", 'pub dv_IndexedLabel'[Label3]),
SELECTCOLUMNS('pub dv_IndexedLabel', "Label", 'pub dv_IndexedLabel'[Label4]),
SELECTCOLUMNS('pub dv_IndexedLabel', "Label", 'pub dv_IndexedLabel'[Label5])))

 

and I can list the Internal IDs,

SELECTCOLUMNS('pub dv_IndexedLabel', "InternalID", 'pub dv_IndexedLabel'[InternalID])

 

But am having trouble getting them together.  Any help you can provide would be amazing!!!

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @Anonymous ,

 

It is suggested to use "UnPivot". In Power Query Editor, try to select "Internal ID" column and then go to "Transform" -> "UnPivot Columns" -> "UnPivot other columns".

 

Reference: Pivot and Unpivot with Power BI

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Icey
Community Support
Community Support

Hi @Anonymous ,

 

It is suggested to use "UnPivot". In Power Query Editor, try to select "Internal ID" column and then go to "Transform" -> "UnPivot Columns" -> "UnPivot other columns".

 

Reference: Pivot and Unpivot with Power BI

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Greg_Deckler
Super User
Super User

@Anonymous - Having trouble visualizing what is going on. Can you provide sample data and expected output? 

 

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ 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...
Anonymous
Not applicable

Hope this helps;

Here is my sample data in the original format;

Internal IDLabelLabel2Label3Label4Label5
16017SmithHKSWill  
12345Perkins    
19447GHTWill   
17215PowerCommonAP  

 

This is the expected transformation;

16017Smith
16017HKS
16017Will
12345Perkins
19447GHT
19447Will
17215Power
17215Common
17215AP

 

I am able to create a single column with all labels and I am able to create the Internal ID, but not together.

@Anonymous 

You can do it in Power Query, Paste the code below in a Blank Query>Advanced Editor and follow the simple steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQzMDRX0lEKzs0syQDSHt7BQDI8MydHKVYHKG1kbGIKFAhILcrOzCsGshTAGCxnaWIC0uruEQLTApcyNzIEa8svTy0C0s75ubn5eUCGY4BSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Internal ID" = _t, Label = _t, Label2 = _t, Label3 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Internal ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> " ")),
    #"Merged Columns" = Table.CombineColumns(#"Filtered Rows",{"Internal ID", "Value"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"New Column")
in
    #"Merged Columns"

Fowmy_0-1599245955308.png

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Sorry - My copy pasta from Excel - Joined the 2 columns into one.  The end result should be

InternalID || Label


16017 || Smith
16017 || HKS
16017 || Will
12345 || Perkins
19447 || GHT
19447 || Will
17215 Power
17215 Common
17215 AP

@Anonymous 

Try this please:

Fowmy_0-1599248225706.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQzMDRX0lEKzs0syQDSHt7BQDI8MydHKVYHKG1kbGIKFAhILcrOzCsGshTAGCxnaWIC0uruEQLTApcyNzIEa8svTy0C0s75ubn5eUCGY4BSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Internal ID" = _t, Label = _t, Label2 = _t, Label3 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Internal ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> " ")),
    #"Merged Columns" = Table.CombineColumns(#"Filtered Rows",{"Internal ID", "Value"},Combiner.CombineTextByDelimiter("||", QuoteStyle.None),"New Column")
in
    #"Merged Columns"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn



 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Thank you, but I need them in 2 separate columns

@Anonymous 

Here you go . . .

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQzMDRX0lEKzs0syQDSHt7BQDI8MydHKVYHKG1kbGIKFAhILcrOzCsGshTAGCxnaWIC0uruEQLTApcyNzIEa8svTy0C0s75ubn5eUCGY4BSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Internal ID" = _t, Label = _t, Label2 = _t, Label3 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Internal ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> " "))
in
    #"Filtered Rows"

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

amitchandak
Super User
Super User

@Anonymous , union, and select column should work fine.

To use join all these fine with the label on the new table. The only one will be active.

Use userelation to create another formula

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Anonymous
Not applicable

Thank you @amitchandak for your prompt response!

 

I'm not sure that the table relations would work as there could be duplicates in some fields....unless I may be missing something?

 

When I try to put them together I am getting a too few agruement error  

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.