Excel Power Query

[gheyman]

I converted a table to a List and named it "SelectedProposal"

let
Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Proposal Id", type text}, {"Proposal", type text}, {"Version", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Proposal", "Version"}),
#"Proposal Id" = #"Removed Columns"[Proposal Id]
in
#"Proposal Id"

I am using this "List" to filter down a table to just items that match what's selected in the List

let
Source = Sql.Database("Davms221208", "PROPRICER_NIS_Reporting"),
ModelPropricer_vdataProposalMaterial = Source{[Schema="ModelPropricer",Item="vdataProposalMaterial"]}[Data],
#"Filtered Rows" = Table.SelectRows(ModelPropricer_vdataProposalMaterial, each List.Contains(SelectedProposal,[ParentId])),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"ResourceId"}, #"ModelPropricer vdataPPResource", {"Id"}, "ModelPropricer vdataPPResource", JoinKind.LeftOuter),
#"Expanded ModelPropricer vdataPPResource" = Table.ExpandTableColumn(#"Merged Queries", "ModelPropricer vdataPPResource", {"Name"}, {"ModelPropricer vdataPPResource.Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded ModelPropricer vdataPPResource",{{"ModelPropricer vdataPPResource.Name", "Resource"}}),
#"Merged Queries1" = Table.NestedJoin(#"Renamed Columns", {"ParentMaterialId"}, #"Renamed Columns", {"Id"}, "Renamed Columns", JoinKind.LeftOuter),
#"Expanded Renamed Columns" = Table.ExpandTableColumn(#"Merged Queries1", "Renamed Columns", {"Name"}, {"Name.1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Renamed Columns",{{"Name.1", "Assembly"}}),
#"Merged Queries2" = Table.NestedJoin(#"Renamed Columns1", {"PrpTaskId"}, #"ModelPropricer vdataProposalTask", {"Id"}, "ModelPropricer vdataProposalTask", JoinKind.LeftOuter),
#"Expanded ModelPropricer vdataProposalTask" = Table.ExpandTableColumn(#"Merged Queries2", "ModelPropricer vdataProposalTask", {"Name", "Description", "Quantity", "MTRL CONSOLIDATION"}, {"Name.1", "Description.1", "Quantity", "MTRL CONSOLIDATION"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded ModelPropricer vdataProposalTask",{{"Name.1", "Task"}, {"Description.1", "Task Description"}, {"Quantity", "Task Quantity"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns2",{{"Task", Order.Ascending}, {"MakeBuy", Order.Ascending}}),
#"Renamed Columns3" = Table.RenameColumns(#"Sorted Rows",{{"MakeBuy", "Level"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns3",{{"UnitQty", Int64.Type}}),
#"Added Custom Extended Qty" = Table.AddColumn(#"Changed Type", "Extended Qty", each [UnitQty] *[Task Quantity]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom Extended Qty",{{"Extended Qty", Int64.Type}}),
#"Merged Queries3" = Table.NestedJoin(#"Changed Type1", {"Name", "MTRL CONSOLIDATION"}, #"ModelPropricer vdataProposalMaterial (Group)", {"Name", "MTRL CONSOLIDATION"}, "ModelPropricer vdataProposalMaterial (Group)", JoinKind.LeftOuter),
#"Expanded ModelPropricer vdataProposalMaterial (Group)" = Table.ExpandTableColumn(#"Merged Queries3", "ModelPropricer vdataProposalMaterial (Group)", {"Consolidated Qty"}, {"Consolidated Qty"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded ModelPropricer vdataProposalMaterial (Group)",{{"Consolidated Qty", Int64.Type}}),
#"Reordered Columns3" = Table.ReorderColumns(#"Changed Type2",{"Task", "Task Description", "Level", "Assembly", "Name", "Description", "UnitQty", "Task Quantity", "Extended Qty", "Consolidated Qty", "MTRL CONSOLIDATION", "Resource", "Id", "ParentId", "ParentMaterialId", "PrpProductId", "PrpTaskId", "MaterialId", "PartName", "PartDesc", "PartNumber", "ResourceId", "ResFieldCombId", "PrpResFieldCombId", "BurPoolId", "Fee", "RateBandId", "Comments", "UM", "CurveId", "SpreadMethod", "SpreadStartDate", "SpreadEndDate", "SpreadAmount", "SpreadPrecision", "UnitCost", "CurrencyId", "EscalationFactor", "TargetFactor", "VolumeFactor", "CostSource", "MatCostSourceId", "CostRule", "CostRuleId", "ShipQty", "InventoryQty", "MfgYieldPercent", "DestructiveEvalQty", "Text1", "Text2", "Text3", "Text4", "Flags", "FromQty", "ToQty", "ConditionalCol0", "ConditionalCol1", "CalendarId"}),
#"Filtered Rows1" = Table.SelectRows(#"Reordered Columns3", each ([Description] <> null))
in
#"Filtered Rows1"


Is there a way to code this so that if the List (SelectedProposal) is null to show everything in the table? (Basically not filtering it).

Thanks for the help - I hope this the correct forum since there isn't one for Excel Power Query.

 

[RoryA]

This is the correct forum

I'm assuming the list actually contains one null item rather than being empty per se, in which case something like:

#"Filtered Rows" = Table.SelectRows(ModelPropricer_vdataProposalMaterial, each List.Contains(SelectedProposal,[ParentId]) or List.NonNullCount(SelectedProposal)=0),

should do the trick?

 

[gheyman]

Thank you very much!