Mapping Issue

[irfan_abdrhman]

Hello community,

I have an issue for this power BI where I want the SSI Item + SSI Type/Grade ( as a pair ) to be mapped with ST Item + ST Type/Grade (as a pair) where if they currently share the same Yard Location which is why they are in the same row of items, but if they are not mapped together, different values either item is different or Type/Grade is different then the pair ST Item + ST Type/Grade is pushed to the next row along with Yard Location and it's value ST Quantity.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Bimage Consulting\Downloads\Irfan.xlsx"), true, true),
     Source_Sheet = Source{[Item="Source (R&D) R1",Kind="Sheet"]}[Data],
    FilteredRows = Table.SelectRows(Source_Sheet, each ([#"Yard Location"] <> null)),
    ColNames = Table.ColumnNames(FilteredRows),
    __PreserveColumns = {"Form ID", "Yard Location", "Form Link"},
    Transform =
      List.Transform({"SSI", "ST"}, (var)=>
        [ a = List.Select(ColNames, (x)=> Text.StartsWith(x, var)), //Select Columns
          b = List.Select(a, (x)=> Text.Contains(x, "Item", Comparer.OrdinalIgnoreCase)), //Select contains "Supplier"
          pairs = List.Count(a) / List.Count(b),
          c = List.TransformMany(
                  List.Transform(List.Zip({ Table.ToRows(Table.SelectColumns(FilteredRows, __PreserveColumns)), Table.ToRows(Table.SelectColumns(FilteredRows, a)) }), List.Combine),
                  each List.Split(List.Skip(_, List.Count(__PreserveColumns)), pairs),
                  (x,y) => List.FirstN(x, List.Count(__PreserveColumns)) & y ),
          d = Table.FromRows(c, __PreserveColumns & List.FirstN(a, pairs)),
          e = Table.TransformColumnNames(d, each Text.Trim(Text.Remove(_, {"0".."9"})))
        ][e]
      ),
    Transform2 = List.FirstN(Transform, 1) & List.Transform(List.Skip(Transform), (x)=> Table.RemoveColumns(x, __PreserveColumns)),
    Combine = [ a = List.Combine(List.Transform(Transform2, (x)=> Table.ColumnNames(x))), //Column Names
    b = List.Combine(List.Transform(Transform2, Table.ToColumns)),
    c = Table.FromColumns(b, a)
  ][c],
    FilteredRows2 = Table.SelectRows(Combine, each ([SSI Item] <> null)),
    RemovedOtherColumns = Table.SelectColumns(FilteredRows2,{"Form ID", "Yard Location", "SSI Supplier Name", "SSI Item", "SSI Type/Grade", "SSI Quantity", "SSI DO No", "SSI DO Date", "ST Location", "ST Quantity"}),
    RenameColumns1 = Table.TransformColumnNames(RemovedOtherColumns, each Text.Combine(List.RemoveMatchingItems(Text.Split(_, " "), {"SSI", "ST"}), " ")),
    RenamedColumns2 = Table.RenameColumns(RenameColumns1,{{"Quantity1", "Stock Take"}}),
    #"Changed Type" = Table.TransformColumnTypes(RenamedColumns2,{{"Quantity", Int64.Type}, {"Stock Take", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Form ID", "Supplier Name", "Item", "Type/Grade", "Yard Location", "Quantity", "DO No", "DO Date", "Location", "Stock Take"}),
    #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Custom", each if [Supplier Name] = "SUPPLIER 1" then 1 else if [Supplier Name] = "SUPPLIER 2" then 2 else if [Supplier Name] = "SUPPLIER 3" then 3 else if [Supplier Name] = "SUPPLIER 4" then 4 else if [Supplier Name] = "SUPPLIER 5" then 5 else if [Supplier Name] = "SUPPLIER 6" then 6 else if [Supplier Name] = "SUPPLIER 7" then 7 else if [Supplier Name] = "SUPPLIER 8" then 8 else if [Supplier Name] = "SUPPLIER 10" then 10 else if [Supplier Name] = "SUPPLIER 11" then 11 else if [Supplier Name] = "SUPPLIER 12" then 12 else if [Supplier Name] = "SUPPLIER 13" then 13 else null),
    #"Added Index" = Table.AddIndexColumn(#"Added Conditional Column", "Index", 0, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Index"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Stock Balance", each [Quantity]-[Stock Take])
in
    #"Added Custom"

 Here is the source code, and the link to the source file : Please Click this Link 
For the Source File, view sheet Source (R&D) R1 and Table 2 for expected outcome only, to not get confuse. Sorry for the confusion.


Your help is most appreciated, thank you.

[v-xinruzhu-msft]

Hi @irfan_abdrhman 

Based on the data you have offered, you have multiple SSI Type and ST Type, i have a questions that if each type of SSI and ST need to be moved to next row if they cannot map? 

Best Regards!

Yolo Zhu

[irfan_abdrhman]

May I know if it's possible?

[irfan_abdrhman]

Hi @v-xinruzhu-msft 
Yes, that's correct, if ST (Item and Type/Grade) is unable to map with existing SSI (Item and Type/Grade) then will create a new row where it has the, common identifier : Yard Location and Form ID. Then once it is pushed to the next row, the initial row will contain the same common identifier and SSI items. 

Additional information : The reason why it is together in the same row in the first place despite having different/same (Item and Type/Grade) is because of the extraction of data in a way to get the Form ID and Yard Location which is the common factor for particular item. But now I want to separate or merge them together as seen as the expected outcome, see google sheets : Table 2

Best Regards,
Irfan

[irfan_abdrhman]

@dufoq3 need your help on this, please