Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!!!
Solved! Go to Solution.
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.
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.
@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.
Hope this helps;
Here is my sample data in the original format;
Internal ID | Label | Label2 | Label3 | Label4 | Label5 |
16017 | Smith | HKS | Will | ||
12345 | Perkins | ||||
19447 | GHT | Will | |||
17215 | Power | Common | AP |
This is the expected transformation;
16017 | Smith |
16017 | HKS |
16017 | Will |
12345 | Perkins |
19447 | GHT |
19447 | Will |
17215 | Power |
17215 | Common |
17215 | AP |
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"
________________________
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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:
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
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
User | Count |
---|---|
122 | |
109 | |
96 | |
59 | |
57 |
User | Count |
---|---|
143 | |
119 | |
102 | |
71 | |
61 |