Forum Discussion
Basic Merge questions.
Hi there,
I am trying to create a Query joining/connecting data from two input query's.
The first query contains a row for each project with basic information
The second query contains the written hours, each row representing Project number, Date and amount of hours. So it is a 1 on N relation.
In a merge I expected to get all the projects as output, regardless if hours had been written in the relevant time frames.
But now I only get an output for the projects that do have time written on.
I am using the Left Outer Join,
Where am I going wrong?
Source = Excel.CurrentWorkbook(){[Name="Basis_Selectie_1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Projectnummer", type text}, {"PM", type text}, {"Project Name", type text}, {"Curtomer", type text}, {"Domein", type text}, {"Project Manager NB", type text}, {"Sales Executive", type text}, {"Organisation", type text}, {"Date contract", type datetime}, {"Status", type text}, {"Project type", type text}, {"Ordered", type number}, {"Realisation", type number}, {"Difference Hours", type number}, {"POC on Hours", type number}, {"POC.% of completion turnover", Int64.Type}, {"Precalculation turnover", type number}, {"Actual turnover", type number}, {"Difference Turnover", type number}, {"Start date", type datetime}, {"End date", type datetime}, {"Date remark", type datetime}, {"Status PM", type text}, {"Opmerkingen", type text}, {"Invoice Schedule", type text}, {"Invoice Dates", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Project Manager NB", "Organisation", "Difference Hours", "POC on Hours", "POC.% of completion turnover", "Precalculation turnover", "Actual turnover", "Difference Turnover", "Date remark", "Status PM", "Opmerkingen", "Invoice Schedule", "Invoice Dates"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Start date", type date}, {"End date", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Projectnummer"}, Export_time, {"project number"}, "Export_time", JoinKind.LeftOuter),
#"Expanded Export_time" = Table.ExpandTableColumn(#"Merged Queries", "Export_time", {"date", "time total"}, {"Export_time.date", "Export_time.time total"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Export_time", each ([Export_time.time total] <> null)),
#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"Export_time.date", type date}}),
#"Extracted Month" = Table.TransformColumns(#"Changed Type2",{{"Export_time.date", Date.Month, Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Extracted Month", {"Projectnummer", "PM", "Project Name", "Curtomer", "Domein", "Sales Executive", "Date contract", "Status", "Project type", "Ordered", "Realisation", "Start date", "End date", "Export_time.date"}, {{"Time", each List.Sum([Export_time.time total]), type nullable number}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"Export_time.date", type text}}, "nl-NL"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Export_time.date", type text}}, "nl-NL")[Export_time.date]), "Export_time.date", "Time", List.Sum)
in
#"Pivoted Column"
Castellum812 I believe that's because of the filter step in row 7. That's where you keep all rows where the time value is not equal to null. Hence, projects without hours written to them are filtered out.
Castellum812 I believe that's because of the filter step in row 7. That's where you keep all rows where the time value is not equal to null. Hence, projects without hours written to them are filtered out.
That did the trick.
I had entered that because the 'Null' values prevented the Pivot from working.
I now replaced the 'Null' with '0'Is that the way to go?
let Source = Excel.CurrentWorkbook(){[Name="Basis_Selectie_1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Projectnummer", type text}, {"PM", type text}, {"Project Name", type text}, {"Curtomer", type text}, {"Domein", type text}, {"Project Manager NB", type text}, {"Sales Executive", type text}, {"Organisation", type text}, {"Date contract", type datetime}, {"Status", type text}, {"Project type", type text}, {"Ordered", type number}, {"Realisation", type number}, {"Difference Hours", type number}, {"POC on Hours", type number}, {"POC.% of completion turnover", Int64.Type}, {"Precalculation turnover", type number}, {"Actual turnover", type number}, {"Difference Turnover", type number}, {"Start date", type datetime}, {"End date", type datetime}, {"Date remark", type datetime}, {"Status PM", type text}, {"Opmerkingen", type text}, {"Invoice Schedule", type text}, {"Invoice Dates", type any}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Project Manager NB", "Organisation", "Difference Hours", "POC on Hours", "POC.% of completion turnover", "Precalculation turnover", "Actual turnover", "Difference Turnover", "Date remark", "Status PM", "Opmerkingen", "Invoice Schedule", "Invoice Dates"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Start date", type date}, {"End date", type date}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Projectnummer"}, Export_time, {"project number"}, "Export_time", JoinKind.LeftOuter), #"Expanded Export_time" = Table.ExpandTableColumn(#"Merged Queries", "Export_time", {"date", "time total"}, {"Export_time.date", "Export_time.time total"}), #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Export_time",{{"Export_time.date", type date}}), #"Extracted Month" = Table.TransformColumns(#"Changed Type2",{{"Export_time.date", Date.Month, Int64.Type}}), #"Grouped Rows" = Table.Group(#"Extracted Month", {"Projectnummer", "PM", "Project Name", "Curtomer", "Domein", "Sales Executive", "Date contract", "Status", "Project type", "Ordered", "Realisation", "Start date", "End date", "Export_time.date"}, {{"Time", each List.Sum([Export_time.time total]), type nullable number}}), #"Replaced Value" = Table.ReplaceValue(#"Grouped Rows",null,0,Replacer.ReplaceValue,{"Export_time.date"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Time"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Replaced Value1", {{"Export_time.date", type text}}, "nl-NL"), List.Distinct(Table.TransformColumnTypes(#"Replaced Value1", {{"Export_time.date", type text}}, "nl-NL")[Export_time.date]), "Export_time.date", "Time", List.Sum), #"Replaced Value2" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"1", "2", "0"}), #"Removed Columns1" = Table.RemoveColumns(#"Replaced Value2",{"0"}) in #"Removed Columns1"
