Expand columns after query merge is updating the column values

[vsurubotla]

Hi , I am trying to merge two tables based on ID column. After merge i am expanding the table to get two date columns D1 and D2, which are also there in two tables that i am merging. But after expanding value of the D2 column in first table is getting updated.

I tried different ways but unable to find out why the value is getting updated during merge.Its Date/Time type column.

Thanks,

Vijay.

[BhaveshPatel]

Are you using the correct join?? What do you meany by updated D2 column values?

[vsurubotla]

Hi,

I am using leftouter join. Data of the left table is getting udpated.Please find below my sample data.

Table 1:

ID(Int)             D1(Date)                   D2(Date)

---------------------------------------------------

1                     9/26/2016               9/28/2016

Table2:         D1(Date)                   D2(Date)                               

----------------------------------------------------

1                12/26/2016                12/28/2016

In Merge step data is showing as below :

ID(Int)             D1(Date)                   D2(Date)                  NewColumn

---------------------------------------------------------------------------

1                    9/26/2016             9/28/2016                       Table

After Expand:

ID(Int)             D1(Date)                   D2(Date)                  NewColumn.D1       NewColumn.D2

----------------------------------------------------------------------------------------------------

1                   9/26/2016             9/22/2016                   9/22/2016             12/26/2016

I am not sure whether any formula is getting applied. when i try to verify in the advance filter, I am not seeing any transformation  or formula on these columns. Onething i don't select the common columns D1,D2 during expand i am seeing the D2 column is updating to 9/22/2016 instead of 12/28/2016. 

Can you please let me know if there is any way to verify the global level formulas that can be applied on specific columns.

Thanks,

Vijay.

[v-huizhn-msft]

Hi @vsurubotla,

I merge Table1 and Table2 using leftouter join, and get expected result as follows.

First, select the Id field to match the second table ID field.

Click OK, get the following result.

I expand the Table, I get the expected result as the screenshot below. And I post my Power Query statement.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDTTt9Q3MkNiWyjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, D1 = _t, D2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"D1", type date}, {"D2", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"ID"},Table4,{"ID"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"D1", "D2"}, {"NewColumn.D1", "NewColumn.D2"})
in
#"Expanded NewColumn"

For your issue, I am not able to reproduce your scenario, could you please share your Query for further analysis? Please click “Advanced Editor” in query Editor window and get the Query statement.

Best Regards,
Angelia