Forum Discussion

noyesae's avatar
noyesae
Regular Visitor
5 years ago
Solved

Identifying Results of a Power Query

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.