Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a table that looks like this.
Name | ID | Project | BL1 desc | BL1 purchase | BL1 sales | BL2 desc | BL2 purchase | BL2 sales |
Mike | 5114 | E | Cisco | 100 | 200 | vmware | 900 | 1300 |
Ann | 9871 | F | Microsoft | 600 | 700 | Cisco | 50 | 90 |
Jeff | 6874 | A | vmware | 200 | 600 |
(this is just an example, in reality there are 6 BL's with +/- 20 different descriptions)
As you can see it's not really normalized, and I want to put the BL data underneath each other.
So for the example above it should be:
Name | ID | Project | BL desc | BL purchase | BL sales |
Mike | 5114 | E | Cisco | 100 | 200 |
Mike | 5114 | E | vmware | 900 | 1300 |
Ann | 9871 | F | Microsoft | 600 | 700 |
Ann | 9871 | F | Cisco | 50 | 90 |
Jeff | 6874 | A | vmware | 200 | 600 |
How can I achieve this? I tried with transpose and unpivot, but I'm unable to get the data straight 😞
Thanks!
Solved! Go to Solution.
Hi @michelp ,
Let's say the name of your main query (table) is Data.
So I would recommend to use next logic. Create two separate tables for BL1 and BL2 from Data table and do an append as new of them. And disable load of all tables except the appended.
In more details
Go to PowerQuery (Edit Queries)
Create table BL1:
1. Right click on Data table-> Reference. Name new query as BL1.
2. Delete all columns which belong to BL2 (BL2 desc, BL2 purchase, BL2 sales).
3. Rename BL1 columns by removing postfix digit (it is 1). So BL1 desc will become BL desc.
The same way we use to create BL2.
Create table BL2:
1. Right click on Data table-> Reference. Name new query as BL2.
2. Delete all columns which belong to BL1.
3. Rename BL2 columns by removing postfix digit.
Create Result table:
1. Select BL1 table -> Append Queries menu -> Append Queries as New.
Also, in order to save space you can right click on Data, BL1 and BL2 and uncheck 'Enable load'.
And here is PBIX for the reference if you need - link.
Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!
You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups
Hi
I hope you can help me. I have tried to unpivot, transform but not success.
Basically, I have a SQL data that recorded the Day, Date and Hours for each Task or Customer. However, this Task or Customer can be repeated for next day or next week. I would like to have it arrange in Power BI model that I can calculate the Total Hours related to each customer or project, etc.
Please see Photo 1 which is the original from SQL database and Photo 2 which is what I needed in Power BI.
Thank you very much for your help and support in advance.
Hi,
Copy this power Query formula.
And apply the logic to your data.
//Unpivot all other columns except the datecolumns,
and then create new column with if-statement against the "attribute" column so we can place the hours in the correct column.//
/Adam
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution
Hi,
You need to change the output to column (not "text"). "sun", "mon" and so on.
I did some for you in this formula, but i dont have all your data.
= Table.AddColumn(#"Unpivoted Other Columns", "Worked Hours", each if [Attribute] = "SunDate" then [sun] else if [Attribute] = "MonDate" then [mon] else if [Attribute] = "TueDate" then [tue] else if [Attribute] = "WedDate" then "Wed" else if [Attribute] = "ThrDate" then "Thr" else if [Attribute] = "FriDate" then "Fri" else if [Attribute] = "SatDate" then "Sat" else null)
/Adam
Hello Adam
Wonderful. You had helped me to solve this crazy problem.
Yes, after I changed that to columns then it worked. Where do I mark problem solve?
I appreciate very much for your help and have a nice day.
I dont think you can mark this as solved becuse you wrote in an old forum post that has already been solved 🙂
Have great day!
Hi Adam
I believe this solution is very helpful and I hope someone has a problem like me can see your solution.
Thanks again very much.
Hi,
Picture 1 is not clear. Share data in a format that can be pasted in an MS Excel file. Please show the expected result very clearly for the data that you share.
Hi
I am not sure how to that. That was the only way that allowed me to attached a picture.
Basically, picture 1 shows I have 5 date columns and 5 hours columns. When I try to put them look like picture 2 in Power BI Query, I could not do it. After unpivot or transform, the total hours is not correct. Thanks.
Create a new table like
new table=
union(summarize(table,table[Name,ID],table[Project],table[BL1 desc],table[BL1 purchase],table[BL1 sales]),
summarize(table,table[Name,ID],table[Project],table[BL2 desc],table[BL2 purchase],table[BL2 sales])
)
rename the coulmn of new table as per need.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi @michelp ,
Let's say the name of your main query (table) is Data.
So I would recommend to use next logic. Create two separate tables for BL1 and BL2 from Data table and do an append as new of them. And disable load of all tables except the appended.
In more details
Go to PowerQuery (Edit Queries)
Create table BL1:
1. Right click on Data table-> Reference. Name new query as BL1.
2. Delete all columns which belong to BL2 (BL2 desc, BL2 purchase, BL2 sales).
3. Rename BL1 columns by removing postfix digit (it is 1). So BL1 desc will become BL desc.
The same way we use to create BL2.
Create table BL2:
1. Right click on Data table-> Reference. Name new query as BL2.
2. Delete all columns which belong to BL1.
3. Rename BL2 columns by removing postfix digit.
Create Result table:
1. Select BL1 table -> Append Queries menu -> Append Queries as New.
Also, in order to save space you can right click on Data, BL1 and BL2 and uncheck 'Enable load'.
And here is PBIX for the reference if you need - link.
Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!
You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups
Hi,
Maybe this can help you.
Copy this code into a blank powerQuery.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VU7JDoIwEP0V0jMHWsXiEbdEI8Z7w6GpbUCFmtbl950ZcTu8ZSZvFqXYTneWpWy9ANoHf7TmCm625cnBRjPYyy2YRkc7lFGfbSQvvinxnxJDqk4Vq9oTNnPOxyBLwLyNxoPyLAMWxPfuoQPmplTyEQhOl32PzUJykBWgak3w0Tt8dEJZSfxemme0hIY31jmMFRJPl4DkB5+Trw9wWV0/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"BL1 purchase", Int64.Type}, {"BL1 sales", Int64.Type}, {"BL2 purchase", Int64.Type}, {"BL2 sales", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Name", "ID", "Project", "BL2 desc", "BL1 desc"}, "Attribute", "Value"),
#"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Text.StartsWith([Attribute], "BL1") then [BL1 desc] else if Text.StartsWith([Attribute], "BL2") then [BL2 desc] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"BL1 desc", "BL2 desc"})
in
#"Removed Columns"
then you have the "Attribute" that you can filter och use as a legend with nice colors and stuff :).
and only one value for your measures instead of 2 value columns. (less measures)
/Adam
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |