Mapping inside of Power Query

[irfan_abdrhman]

Hello Community, 

I want to know if my mapping problem could be done inside Power Query/ Power BI. Click Here for the File. The File consists of Source, Expected Table 1, Expected Table 2, Expected Table 3 and Expected Outcome.

In the Source Sheet, it consists of repetitive columns such as SB Material 1, SB Material 2,.. SSI Material 1, SS Material 2,.. and so on, 

where I want to Separate and Combine the SB, SSI and ST in each respective tables in order to properly combine them in another table. *Any alternative suggestions would be kindly appreciated. This is to ensure that in the Expected Outcome Table, the Material and Type/Grade for SB and ST will be mapped to the existing Material and Type/Grade provided by SSI.

Supplier Name to DO Date will be from SSI, Location and Stock Take will be from ST and Balance Quantity will be from SB. 

Mapping :
Supplier Name - Supplier Name  ( Neutral Data as it does not belong to SSI, ST or SB, duplicated according to the SSI data, fill in line by line)
Material - SSI Material 1,2 ...
Type/Grade - SSI Type/Grade 1,2 ...

Yard Location - Yard Location ( Neutral Data as it does not belong to SSI, ST or SB, duplicated according to the SSI data, fill in line by line)

Quantity - SSI Quantity 1,2 ...
DO No - SSI DO No 1, 2 ...

DO Date - SSI Date 1, 2 ...

Location - ST Location 1,2 ...( ST Material 1, 2 ... and ST Type/Grade 1, 2 ... is mapped with SSI )

Stock Take - ST Quantity 1, 2 ... ( ST Material 1, 2 ... and ST Type/Grade 1, 2 ... is mapped with SSI )

Balance Quantity - SB Quantity 1, 2 ... ( SB Material 1, 2 ... and SB Type/Grade 1, 2 ... is mapped with SSI )

*If there is any workaround for this, would be appreciated as long as the result can be achieved from the source sheet.

Thank you, and I hope for a solution.

[dufoq3 · Accepted solution]

Hi, it will work but you have to have all columns in source. For instance I had to add ST Location 4 and ST Quantity 4. Second issue is with naming. For some columns you have SB Material but for no 4 you have SB_Item 4(you have to rename SB_Item 4 to SB Matierial 4).

Result:

If you want to preserve more columns (like Link), you have to define them here:

Code for Source EXCEL file:

let
    Source = Excel.Workbook(File.Contents("C:\Downloads\PowerQueryForum\irfan_abdrhman\MIR.xlsx"), true, true),
    Source_Sheet = Source{[Item="Source",Kind="Sheet"]}[Data],
    FilteredRows = Table.SelectRows(Source_Sheet, each ([#"Delivery/DO Date"] <> null)),
    ColNames = Table.ColumnNames(FilteredRows),
    __PreserveColumns = {"Delivery/DO Date", "Supplier Name", "Yard Location"},
    Transform =
      List.Transform({"SSI", "ST", "SB"}, (var)=>
        [ a = List.Select(ColNames, (x)=> Text.StartsWith(x, var)), //Select Columns
          b = List.Select(a, (x)=> Text.Contains(x, "Material", Comparer.OrdinalIgnoreCase)), //Select contains "Material"
          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 Material] <> null)),
    RemovedOtherColumns = Table.SelectColumns(FilteredRows2,{"Supplier Name", "SSI Material", "SSI Type/Grade", "Yard Location", "SSI Quantity", "SSI DO No", "SSI Date", "ST Location", "ST Quantity", "SB Quantity"}),
    RenameColumns1 = Table.TransformColumnNames(RemovedOtherColumns, each Text.Combine(List.RemoveMatchingItems(Text.Split(_, " "), {"SSI", "ST", "SB"}), " ")),
    RenamedColumns2 = Table.RenameColumns(RenameColumns1,{{"Quantity1", "Stock Take"}, {"Quantity2", "Balance Quantity"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns2,{{"Supplier Name", type text}, {"Material", type text}, {"Type/Grade", type text}, {"Yard Location", type text}, {"Quantity", type number}, {"DO No", type text}, {"Date", type date}, {"Location", type text}, {"Stock Take", type number}, {"Balance Quantity", type number}})
in
    ChangedType

Code with sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7Zhfb4IwEMC/CsFXF9vSoj7iYI5EnAFdshjf9MHMuCWb338UaG2RUv80yuZePC3H3fV+3NFzPrcB7ADcQQBhu23PHvzYC8fpNwRA+ukCEEXSuqNYh0SxDrIbIDq40kovwVRKAajMU2VElZGgTGqUcVlZtSGq7FJlR1BWR52E08DycpVKe1xB5TBTGGThV17f7jabveAhJLPJZBQGsUVz9ubFfubk5XO1tdZby9t9fyxXX+/WYLfeLO1Fey5vaG8+T1q/sCo7O1kIaUzj7FUDMuSrueIQzUCDBleiKZ46ZRGdnkiZT1/xmFcUBQIQKLR/N07OCjFWUMOK1LEyX0dO0RXJX8v8KXAcBgdp4LjHFJJRONlbyFUXUSMSeR04jgZOrw4Of7EZpUNDkvwa9tZcwfFghudRg6d/3caGi0NZ954bG4fj18OB4LqNDeeNTXJ7d42Nwwk0cMSJZeRNgzg90tvZcRfkK5cHJZilcAjlAUtwjDk7Kyw+6Jjcdj0gwgA9aQChakBGk1YiRGdIye+NCN2selwGZ6iBI84UUTgOx0PLnyVTIWVR8fvC0GTjLZKfrGF5pjHosbmCY+oyTM/NxYT+MfWOxSTOicPYew1GQr74woVhcTstt6BTHk9Nemuu2P/TAxgfXMNn8QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Delivery/DO Date" = _t, #"SB Material 1" = _t, #"SB Quantity 1" = _t, #"SB Type/Grade 1" = _t, #"SB Material 2" = _t, #"SB Quantity 2" = _t, #"SB Type/Grade 2" = _t, #"SB Material 3" = _t, #"SB Quantity 3" = _t, #"SB Type/Grade 3" = _t, #"SB Material 4" = _t, #"SB Quantity 4" = _t, #"SB Type/Grade 4" = _t, #"SSI Material 1" = _t, #"SSI DO No 1" = _t, #"SSI Date 1" = _t, #"SSI Quantity 1" = _t, #"SSI Type/Grade 1" = _t, #"SSI Material 2" = _t, #"SSI DO No 2" = _t, #"SSI Date 2" = _t, #"SSI Quantity 2" = _t, #"SSI Type/Grade 2" = _t, #"SSI Material 3" = _t, #"SSI DO No 3" = _t, #"SSI Date 3" = _t, #"SSI Quantity 3" = _t, #"SSI Type/Grade 3" = _t, #"SSI Material 4" = _t, #"SSI DO No 4" = _t, #"SSI Date 4" = _t, #"SSI Quantity 4" = _t, #"SSI Type/Grade 4" = _t, #"ST Material 1" = _t, #"ST Location 1" = _t, #"ST Quantity 1" = _t, #"ST Type/Grade 1" = _t, #"ST Material 2" = _t, #"ST Location 2" = _t, #"ST Quantity 2" = _t, #"ST Type/Grade 2" = _t, #"ST Material 3" = _t, #"ST Location 3" = _t, #"ST Quantity 3" = _t, #"ST Type/Grade 3" = _t, #"ST Material 4" = _t, #"ST Location 4" = _t, #"ST Quantity 4" = _t, #"ST Type/Grade 4" = _t, #"Supplier Name" = _t, #"Yard Location" = _t, Link = _t]),
    ReplaceNulls = Table.TransformColumns(Source, {}, each if _ = "null" then null else _),
    ColNames = Table.ColumnNames(Source),
    __PreserveColumns = {"Delivery/DO Date", "Supplier Name", "Yard Location"},
    Transform =
      List.Transform({"SSI", "ST", "SB"}, (var)=>
        [ a = List.Select(ColNames, (x)=> Text.StartsWith(x, var)), //Select Columns
          b = List.Select(a, (x)=> Text.Contains(x, "Material", Comparer.OrdinalIgnoreCase)), //Select contains "Material"
          pairs = List.Count(a) / List.Count(b),
          c = List.TransformMany(
                  List.Transform(List.Zip({ Table.ToRows(Table.SelectColumns(ReplaceNulls, __PreserveColumns)), Table.ToRows(Table.SelectColumns(ReplaceNulls, 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 Material] <> null)),
    RemovedOtherColumns = Table.SelectColumns(FilteredRows2,{"Supplier Name", "SSI Material", "SSI Type/Grade", "Yard Location", "SSI Quantity", "SSI DO No", "SSI Date", "ST Location", "ST Quantity", "SB Quantity"}),
    RenameColumns1 = Table.TransformColumnNames(RemovedOtherColumns, each Text.Combine(List.RemoveMatchingItems(Text.Split(_, " "), {"SSI", "ST", "SB"}), " ")),
    RenamedColumns2 = Table.RenameColumns(RenameColumns1,{{"Quantity1", "Stock Take"}, {"Quantity2", "Balance Quantity"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns2,{{"Supplier Name", type text}, {"Material", type text}, {"Type/Grade", type text}, {"Yard Location", type text}, {"Quantity", type number}, {"DO No", type text}, {"Date", type date}, {"Location", type text}, {"Stock Take", type number}, {"Balance Quantity", type number}})
in
    ChangedType

[irfan_abdrhman]

Hi there seems to be an issue, with the source excel file,

and this

when i click on Go to Error, it brings me to the first screenshot, "Transform" Step

[dufoq3]

Yes, I described what you have to do with your source file in my previous post, but it seems you haven't read it yet 😎.

[irfan_abdrhman]

I have created and changed the row name as shown here. May I know your take on this? Because it doesn't seem to be working for me despite changing and adding more columns for the incompleteness.

[dufoq3]

This has to be SSI not just SS

[irfan_abdrhman]

I am so sorry, thank you so much for your help.

[dufoq3]

You're welcome, enjoy 😉

[irfan_abdrhman]

Hello again @dufoq3 , if I made an adjustment whereby i want to add a new column of data, repetitive like column Supplier Name, May I know is it difficult? I want to add a new column called "Form ID", where the cells would have " # " and numbers. See Link

[dufoq3]

Hi, you have to add your column to __PreserveColumns step like I mentioned before, but also add such column to RemoveOtherColumns step. Don't forget to set correct column names, because you have again SS instead of SSI in this new sample (2 columns).