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.