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

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's avatar
ronrsnfld
Super User
2 years ago

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

    • ronrsnfld's avatar
      ronrsnfld
      Super User
      2 years ago

      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's avatar
      ronrsnfld
      Super User
      2 years ago

      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.