Power Query - Fill up -- conditional?

K

kerygma

New member
Joined
Jul 11, 2016
Messages
19
Reaction score
0
Points
0
I don't know how to describe this well, it's a very simple question and my attachment lays it out nicely, I think.

I have my input, report, and desired report... basically I've got multiple rows per person and and their dates in multiple columns, can't figure out how to get one row per person and fill the dates so they are nice and neat. I guess it'll make much more sense if you open up the attached sheet.

Thank you in advance!
 

Attachments

  • Lightning.xlsx
    23.2 KB · Views: 42
Try this:

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Sorted Rows" = Table.Sort(Source,{{"app date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"name"}, {{"app date", each Table.AddIndexColumn(_, "Index",0,1), type table}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"app date"}),
    #"Expanded app date" = Table.ExpandTableColumn(#"Removed Other Columns", "app date", {"app date", "Index", "name"}, {"app date", "Index", "name"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded app date",{{"app date", type date}, {"Index", Int64.Type}, {"name", type text}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Index", type text}}, "en-GB")[Index]), "Index", "app date")
in
    #"Pivoted Column"
 
Awesome, thank you. I went through your steps and mine would have worked had I deleted the extra Index and min-index columns before pivoting. It's nice that yours is all in one step compared to my three steps, but I don't know how to come up with the custom M language like you did :) pretty neat. From your Grouped Rows step, I understood it was a custom one where you grouped by name and then for each grouping added an index... that correct?
Thanks again for your help. For some reason, I didn't get notified by e-mail of the response, so sorry for the late response to your solution.
Cheers!
 
kerygma said:
From your Grouped Rows step, I understood it was a custom one where you grouped by name and then for each grouping added an index... that correct?
Click to expand...
It is.

Happy to Help.
 
You must log in or register to reply here.
Back
Top