Changing lay out of sheet

[jwi1]

Hi,

Below is a simple excel sheet, which I am going to use in my power BI report.

On the top, you see (for example) the 2 blue lines.

I want to change the lay out to the lay out, showed on the bottom.

So, instead of 2 lines, they are  now combined into 1 line.

In reality, the sheet is much bigger, so the GKEY is the bases.

Hope someone can help me with this, thanks upfront for that 😀

John

[Fowmy · Accepted solution]

@jwi1 

Please check the attached file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZBda4MwFIb/inhdJYnfuStWqDdR1CJDilg8LbY2LdEN9u+X7AO2IusG9ibhnITnvOepKh07FvJdx9YXepHFG98jtu2pitiWPDcsLupyyYq8TpMyymqWqLfAQK5BsOZRC8naGF6HEc6Gvl38SsRfxA/WKs7DhLEoLG6YRNZr4Cfz1EPHjw2/ByZTUZ+i/DvXp0h9zkG8gKAtDJ2AQUsYrZ+v7Zjy9pDtxb1B6OcGE/F9iq3/xv+DaexS7MhGZJZd3wLfXdQSl5CPIu0bzkFes+hXgwLZgGYO6ZL27uzR1rHUjj5Tb98A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [gkey = _t, id = _t, event_type_gkey = _t, id_1 = _t, placed_time = _t, creator = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"gkey", Int64.Type}, {"id", type text}, {"event_type_gkey", Int64.Type}, {"id_1", type text}, {"placed_time", type text}, {"creator", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "placed_time_1", each try #"Added Index"[placed_time]{[Index]+1} otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "creator_1", each try #"Added Index"[creator]{[Index]+1} otherwise null),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Custom1", {{"Index", each Number.Mod(_, 2), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Calculated Modulo", each ([Index] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

[jwi1]

Good day @Fowmy,

Thanks a lot, is working!

John

[Fowmy]

@jwi1 

Can you share an Excel file,? you can save it OneDrive or any other cloud storage location and share the link here.

This could be easily solved using Power Query 

[jwi1]

@Fowmy and @amitchandak

Here is the link to the sheet: https://www.dropbox.com/scl/fi/cczmvipftztfmlu7y1uyt/testsheet.xlsx?dl=0&rlkey=7s28yyf76se6gh0ap7p87...

If the event_type_gkey is 243, the nearest (in time) event_type_gkey 241 must be combined with 243.

Some with 242 and 240