Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,

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

hello Anonymous , 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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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