topic Identifying Results of a Power Query in Developer

Identifying Results of a Power Query

noyesae — Thu, 05 Aug 2021 15:27:00 GMT

Hello

The subject I chose for this is probably bad so I apologize for that. I have a table with part numbers and the locations that those part numbers are stored in the warehouse. I created a power query for the off the location column to tell me if that location is a Bin or a Rack. Now what I want to know is which part numbers are located both in the Bins and the Racks but I'm having a difficult time figuring out just how I should do that.

Ultimately the best result would be another column that was a Yes / No for each part number if it was or wasn't stored in both.

Any help you can give is appreciate. Thanks in advance

Re: Identifying Results of a Power Query

lbendlin — Fri, 06 Aug 2021 14:59:55 GMT

Please provide sample data in usable format (not as a picture) and show the expected outcome.

Re: Identifying Results of a Power Query

Anonymous — Tue, 10 Aug 2021 10:16:42 GMT

Hi noyesae

I build a sample to have a test.

Mcode :

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWBsJLALCMgKxnMMobLGkPEYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Part Number" = _t, Location = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Part Number", Int64.Type}, {"Location", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Location"}, #"Bin or Rack", {"Location"}, "Bin or Rack", JoinKind.LeftOuter), #"Expanded Bin or Rack" = Table.ExpandTableColumn(#"Merged Queries", "Bin or Rack", {"Type"}, {"Bin or Rack.Type"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Bin or Rack",{{"Bin or Rack.Type", "Bin or Rack"}}), #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Part Number"}, {{"Count", each Table.RowCount(List.Distinct(_[Bin or Rack])), Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if [Count]=2 then "Yes"else"No"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Result"}}) in #"Renamed Columns1"

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.