cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HBAKER_JGD
New Member

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 @HBAKER_JGD ,

 

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 @HBAKER_JGD ,

 

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

Greg_Deckler
Super User IV
Super User IV

@HBAKER_JGD - 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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

@HBAKER_JGD 

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!

Proud to be a Super User!

Website   YouTube    LinkedIn

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

@HBAKER_JGD 

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!

Proud to be a Super User!

Website   YouTube    LinkedIn

Thank you, but I need them in 2 separate columns

@HBAKER_JGD 

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!

Proud to be a Super User!

Website   YouTube    LinkedIn
amitchandak
Super User IV
Super User IV

@HBAKER_JGD , 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...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.