Transpose or Unpivot

[Centaur]

Hello,

I have a file 

Co Name, Amount and Date are all in separate columns. 

I want:

Date to be transposed to rows but keep the Co Name and Amount in rows

But Group co name. 

I am not sure if this can be done in power query? 

link to file (I cant attach a file it seems): 

https://1drv.ms/x/s!AkvCjNvcBhpQiPRSwgYzmL3mCYepgQ?e=kFLRKu

 Please see sample file, screen shot below as well

thank you very much.

[Anonymous]

is this what you are after?

[Centaur]

Hi Rocco, I think that file is for Power BI (.pbix)?  I do not have that software.  Would you possibly be able to save it as an excel file?  thank you. 

[Anonymous · Accepted solution]

try this (my) last guess!

[Centaur]

Hi Rocco, yes thats it.  thank you.  REally appreciate it.  I am a novice user of PQ.  

[Anonymous]

let
    Source = Excel.Workbook(File.Contents("C:\Users\sprmn\OneDrive\Documents\Power BI Desktop\FCall.xlsx"), null, true),
    FCall_20220325_DefinedName = Source{[Item="FCall_20220325",Kind="DefinedName"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(FCall_20220325_DefinedName, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"LCID", Int64.Type}, {"dte", type date}, {"Amt", type text}, {"ProjName", type text}, {"CompanyName", type text}, {"LCNo", type text}, {"ProjID", Int64.Type}, {"LCName", type text}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"dte", type text}}, "it-IT"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"dte", type text}}, "it-IT")[dte]), "dte", "Amt"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"LCID", "ProjName", "LCNo", "ProjID", "LCName"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"CompanyName", Order.Ascending}})
in
    #"Sorted Rows"

[Centaur]

Hi Tom,

Nice.  Would I be able to apply that to another data source?   The example I posted was a simple and my production file if slightly different. I pasted the code and changed the names accordingly and the data seemed to be the same data meaning that it didnt update to my production data that has several hundred records.  For example, my production file has many different company names but I only still see "Co A" as the grouping.  Not sure if I did something wrong.  I am a novice user of power query.  thank you very much.  

[tackytechtom]

Hi @Centaur , 

Could you share some more data please? 🙂

Thanks!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Centaur]

Hi Tom, I don't think you saw my post below. I have a link to my file. It has all the data actually. I think it's on the second sheet.  It was in my response to Rocco actually (today).  Thank you ver much.  

[tackytechtom]

Hi @Centaur ,

Maybe this one?

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Excel.Workbook(File.Contents("C:\Users\filepath.xlsx"), null, true),
    Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"LCID", Int64.Type}, {"dte", type date}, {"Amt", type number}, {"ProjName", type text}, {"CompanyName", type text}, {"LCNo", type text}, {"ProjID", Int64.Type}, {"LCName", type text}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"dte"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Errors", {{"dte", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Removed Errors", {{"dte", type text}}, "en-GB")[dte]), "dte", "Amt", List.Sum)
in
    #"Pivoted Column"

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Centaur]

Hi Tom / Rocco,

I have looked at both solutions. 

I think that since the source data and the output are both in the same file, I need 

let
Source = Excel.CurrentWorkbook(){[Name="table3"]}[Content],
I tried to modify but I am getting errors that table3 doesnt exist but it does.  The name of the source data table is table3 so I am a little confused.

Could you kindly assist with knowing that both the source and the output are in the same file?   thank you very much.  

[tackytechtom]

Hi @Centaur .

How about this:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs5XcFTSUTI00DMwUAAz9A31jQyMDJVideCyRnBZQyyyxnBZIyyyJnBZiKSRUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CompanyName = _t, Amt = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CompanyName", type text}, {"Amt", Int64.Type}, {"Date", type date}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-GB")[Date]), "Date", "Amt", List.Sum)
in
    #"Pivoted Column"

Let me know, if this helped! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Centaur]

Hi, I think it woudl be better that I provide the complete file.  

here it is on my onedrive.  

[removed link]

sheet 2 has the data.  

let me know if you have any questions.  

If I could add a twist. 

Grouping:

parent:  

ProjID (in 1 row)

then by

LCID (in same row as ProjID)

Company Name (in same row as ProjID)

Amount (in same row as ProjID)

LCName (in same row as ProjID)

then the dates along top 

I hope that makes sense.  

I think its a lot easier than how I am explaining. 

CompanyName01/10/202101/11/202101/12/202101/01/2022

Co A

10

20

30

40

much like the original but adding projID I guess the "parent" grouping.  I need this since there are same companies in different projects.  

[ronrsnfld]

Is this what you want for output from your posted file (obviously with more columns and rows)?

If so, it's just a matter of 

• delete the unwanted columns
• Pivot on the Date column
  • Values = Amt
  • No aggregation

[Centaur]

Hi ronrsnfld, yes I think that is what I want.  I did not know it was that simple.  I was over thinking I think.  thank you very much for the explanation.