Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jaworben
Regular Visitor

transforming excel sheet in power query editor

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:

NotificaitionStat.Date
300642957E00025/22/2024
300634015E00421/24/2024

 

I need it to look like this:

 

NotificationE0002E0042
3006340155/22/20241/24/2024

 

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

Thank you!

- Intern

11 REPLIES 11
jaworben
Regular Visitor

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!

SSofexcel.jpg

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."}), ...

Provided is a screenshot of the email from my boss: 

2024-06-14_12-11-07.jpg


Screenshot of Data, plus another 1000 and some entries, each column has 1310 entries:
2024-06-14_12-12-15.jpg

Screenshot of the format my boss gave me that they wanted:
2024-06-14_12-14-02.jpg

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

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:

 

ronrsnfld_0-1718754245984.png

 

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.

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

vxinruzhumsft_0-1718693942240.png

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.

 

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

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
Super User
Super User

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:

ronrsnfld_0-1718330511824.png

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:

ronrsnfld_1-1718330555176.png

 

 

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
Super User
Super User

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?

HotChilli,

 

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

NotificationE0002E0042
300540151/24/202401/17/2024
3006429575/22/20241/5/2024


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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors