Adding column of calculation with another data sheet

[Anonymous]

I want to add a clolumn with calcurating value with another data sheet.

I have these data sheet (query) and they have these columns.

Sheet "IW58", column "Malfunct.end date" .

Sheet "IW47", column "Act.start date".

I want to do  add a new column in sheet IW58 calcurating "Malfunct.end date"-"Act.start date" so that the

column shows duration how soon the problem was solved.

How should I write the Custom column formula while "Act.start date" is not in the list of Available columns when 

I making custom column?

thanks

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

If you have common column in your two tables, right? If it is, please use the same column to merge two table as one. If there is no common columns, please add an index column(steps in Power Query are same with both excel and Power BI) and merge two table using index column as follows.

Merge Table
2. Add a customer column using Duration-functions which can be found here: https://msdn.microsoft.com/en-us/library/mt296613.aspx?f=255&MSPPError=-2147217396

3. Remove other columns you don't need. You will get right result.

result
Here is my Power Query statement.

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-huizhn\Desktop\New Microsoft Excel Worksheet.xlsx"), null, true),
    IW58_Sheet = Source{[Item="IW58",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(IW58_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Malfunct.end date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},IW47,{"Index"},"IW47",JoinKind.LeftOuter),
    #"Expanded IW47" = Table.ExpandTableColumn(#"Merged Queries", "IW47", {"Act.start date", "Index"}, {"IW47.Act.start date", "IW47.Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded IW47", "Custom", each Duration.Days(Duration.From([Malfunct.end date]-[IW47.Act.start date]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"IW47.Index", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date Difference"}})
in
    #"Renamed Columns"

Best Regards,
Angelia

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

If you have common column in your two tables, right? If it is, please use the same column to merge two table as one. If there is no common columns, please add an index column(steps in Power Query are same with both excel and Power BI) and merge two table using index column as follows.

Merge Table
2. Add a customer column using Duration-functions which can be found here: https://msdn.microsoft.com/en-us/library/mt296613.aspx?f=255&MSPPError=-2147217396

3. Remove other columns you don't need. You will get right result.

result
Here is my Power Query statement.

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-huizhn\Desktop\New Microsoft Excel Worksheet.xlsx"), null, true),
    IW58_Sheet = Source{[Item="IW58",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(IW58_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Malfunct.end date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},IW47,{"Index"},"IW47",JoinKind.LeftOuter),
    #"Expanded IW47" = Table.ExpandTableColumn(#"Merged Queries", "IW47", {"Act.start date", "Index"}, {"IW47.Act.start date", "IW47.Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded IW47", "Custom", each Duration.Days(Duration.From([Malfunct.end date]-[IW47.Act.start date]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"IW47.Index", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date Difference"}})
in
    #"Renamed Columns"

Best Regards,
Angelia

[Anonymous · Accepted solution]

thanks that is a great hint. Mearging, new function I used this time. yes this helped and lead me to the solution.

thank you!