transforming excel sheet in power query editor

[jaworben]

I am new to power query editor. I have an excel sheet with three columns, Notification, Stat., and Date. Each column has 1311 rows. I want the notification column to stay where it is. I need all 1311 rows in the Stat. column to become the 1st row. I then need the entire date column to become rows. 

This is how it looks like:

Notificaition	Stat.	Date
300642957	E0002	5/22/2024
300634015	E0042	1/24/2024

I need it to look like this:

Notification	E0002	E0042
300634015	5/22/2024	1/24/2024

Is this even possible? I have spent 5 hours using transpose and pivot/unpivot columns. 

Thank you!

- Intern

[jaworben]

UPDATE: 
I tried the M-code given in the earlier comment. It got me the correct format, however it got rid of most of my data. Unfortunatly, I now have 6 columns and 268 rows, despite me changing it to "column profiling based on entire data set".
I havent lost hope yet! I still have 8 hours left in the work day! 

Any more ideas to help this new intern out? Thank you!

[ronrsnfld]

Hard to tell for sure without the data and expected outcome wrt the extra rows (and your screenshot is virtually useless to me in that regard), but it might be as simple as adding those other three column to the "key" argument. Basically you would group by all the columns except "Stat." and "Date". See if that works. If not, you'll need to provide more detail.

...
#"Grouped Rows" = Table.Group(#"Changed Type", List.RemoveMatchingItems(Table.ColumnNames(Source),{"Date","Stat."}), ...

[jaworben]

Provided is a screenshot of the email from my boss: 

Screenshot of Data, plus another 1000 and some entries, each column has 1310 entries:


Screenshot of the format my boss gave me that they wanted:


Thank you for being so patient, you have been so helpful!

[ronrsnfld]

I think I see a problem.

Power Query can certainly handle data of your size. 

However, in the screenshot of your result there are several "error" entries.

This is because you have entries with the same Notification and Stat. but multiple dates.

For example:

Here you have the same notification and stat. (E0042) but different dates.

You did not indicate that this was possible, and you have not shown how you would want this displayed.

[v-xinruzhu-msft]

Hi,

Thanks for the solutions @ronrsnfld  and @HotChilli  provided, and i want to offer some more infotmation for use to refer to.

hello @jaworben , you can put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdLLDYQwDATQXjgjxfEnsQvYKlb038ZmCRKJwXDi8jQeO3y/CwEURpO6rMsHgLF9JSEmBORlW7sghixdwF/khBwK7iJXJ4iqTkKuEToNIQhFNKRUykNGa0mh2DPapsUJLeIy/LKn4EiojQeTlC0Ue0ar6Xdx4maKmepz01PwfQ8Gtfnxc7kIqvPF1AkUK4+CuGQ6BIVCXkUZBKfsf1MisC4ouMcpjow3MVxs+wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Notification = _t, Stat = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Notification", Int64.Type}, {"Stat", type text}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Notification", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Notification", "Stat"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "Index"}, {"Date", "Index"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded Count", "Merged", each Text.Combine({[Stat], Text.From([Index], "en-US")}, " "), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Stat", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Merged]), "Merged", "Date"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Notification", "E0043 1", "E0043 2", "E0032 1", "E0042 1", "E0042 2", "E0002 1"})
in
    #"Reordered Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jaworben]

Yes! Technically this is correct! but can this be done with the 1310 rows for those three columns?

Essentially there would be 1311 columns going across and 1310 rows under each column! I dont think power BI is capable of that, nor is M-code.

I think this might be too much to ask for power query editor! But before I tell my boss it cant be done, I figured I reach out again! 

Thank you, Ben

Im gonna try and post the column entries but I think it would exceed the character limit

[ronrsnfld]

I thought you wrote your problem was that you had more columns. This screenshot still only  shows three columns. I don't see how it is helpful for your second issue.

What happened when you tried my suggestion?

[ronrsnfld]

I am assuming that you must have multiple rows with the same notification but different Stat and Date, and that your example is not realistic.

Perhaps a more realistic data source would look like:

That being the case, the below might be what you want:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Notification", Int64.Type}, {"Stat.", type text}, {"Date", type date}}),
    
//will be used below in Pivot
    Stats = List.Sort(List.Distinct(#"Changed Type"[#"Stat."])),

    fieldTypes = List.Repeat({type nullable date}, List.Count(Stats)),
    rowColumnTypes = List.Transform(fieldTypes, (t) => [Type = t, Optional = false]),
    rowType = Type.ForRecord(Record.FromList(rowColumnTypes, Stats),false),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Notification"}, {
        {"Pivot", each Table.Pivot(_, Stats,"Stat.","Date"), type table rowType}}),
    #"Expanded Pivot" = Table.ExpandTableColumn(#"Grouped Rows", "Pivot", Stats)
in
    #"Expanded Pivot"

producing:

[jaworben]

You're a life saver! I am going to try this! I will have to find where I can enter M-code in power query editor! Thank you so much! I will update if this works!

[HotChilli]

I'm reasonably sure you don't really want to do this (in power query at least).

If you did do it and if you want do anything with those new columns, you'll have to write a measure for each column => 1311 measures.

Also, what happened to row with Notification 300642957?

[jaworben]

HotChilli,

The row with notification 300642957 would be moved below the first notification. Atleast, that is what my boss wants. 

Notification	E0002	E0042
30054015	1/24/2024	01/17/2024
300642957	5/22/2024	1/5/2024

What does it mean to write a measure for each column?