Values of columns to rows

[michelp]

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!

[PVu]

Picture 1Picture 2Hi

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. 

[Adamtall]

Hi,

Copy this power Query formula.

Spoiler

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjc0NjIwNTFS0lEKDwYSAUX5KaXJJZn5eQohRYmZOUAhl9TizPQ8IAOkyBSIjUFsAyMDfQMLfQMUjgkyx1QpVocoC5wdXaCmGpJkeiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Customer = _t, project = _t, Task = _t, sun = _t, mon = _t, tue = _t, sundate = _t, mondate = _t, tuedate = _t]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Customer", "project", "Task", "sun", "mon", "tue"}, "Attribute", "Date"),
#"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "Hour", each if [Attribute] = "sundate" then [sun] else if [Attribute] = "mondate" then [mon] else if [Attribute] = "tuedate" then [tue] else 0),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Hour", type number}})
in
#"Changed Type"

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

[Adamtall]

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

[PVu]

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. 

[Adamtall]

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! 

[PVu]

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. 

[Ashish_Mathur]

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.

[PVu]

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. 

[amitchandak]

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

[zoloturu · Accepted 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

[Adamtall]

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