Populate a Column with a Row From Another Column in Power Query

[leimst]

Good afternoon all,

I am working with Power Query and have a need to populate a new column in PQ with the date row from another column in the query. In the attached mock up, I would like to bring the "Observation Date" from the "Question" column and populate the new "Observation Date" column with the date from the "Answer String" column for every instance of the "Student ID" to the far left. I have Googled and read a number of articles concerning this but each is different enough from the case that I am looking at and I am probably not yet accomplished enough in PQ to translate into my situation.


Finally, the data is being sourced from a SQL Server db and I am not able to change or manipulate there so I am left with attempting to accomplish this in PQ or with DAX but I would prefer to accomplish it with Power Query.

Thank you in advance for any help,

leimst

 

[alansidman]

Two Steps:
first: Move the data and show only rows with the data moved.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Section", type text}, {"Question", type text}, {"Answer String", type any}, {"Observation Date", type datetime}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Observation Date", type date}}, "en-US"),
#"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "New Date", each [Answer String]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"New Date", type date}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"New Date"}),
#"Removed Alternate Rows" = Table.AlternateRows(#"Removed Errors",1,1,1)
in
#"Removed Alternate Rows"

Second join the original table with the new table that only has the two records.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Section", type text}, {"Question", type text}, {"Answer String", type any}, {"Observation Date", type datetime}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Student ID", "Question"},Table1,{"Student ID", "Question"},"Table1",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"New Date"}, {"New Date"})
in
#"Expanded Table1"

 

[leimst]

Thank you for your response, I will give this a try!

 

[Nick Burns]

The beauty of PQ, is that you can keep this to one query by merging your "previous steps", thought it requires manipulating the M-Code by clicking on the fx button on the formula bar and adding in the code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Section", type text}, {"Question", type text}, {"Answer String", type any}, {"Observation Date", type datetime}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Observation Date", type date}}, "en-US"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "New Date", each [Answer String]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"New Date", type date}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"New Date"}),
    #"Removed Alternate Rows" = Table.AlternateRows(#"Removed Errors",1,1,1),

    //Next line is created in the formula bar

    #"Merge Tables" = Table.NestedJoin(#"Changed Type",{"Student ID", "Question"}, #"Removed Alternate Rows",{"Student ID", "Question"},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merge Tables", "Table1", {"New Date"}, {"New Date"})
in
    #"Expanded Table1"