topic Re: Mapping inside of Power Query in Power Query

Mapping inside of Power Query

irfan_abdrhman — Mon, 29 Apr 2024 09:39:04 GMT

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.

Re: Mapping inside of Power Query

dufoq3 — Mon, 29 Apr 2024 18:59:14 GMT

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

Re: Mapping inside of Power Query

irfan_abdrhman — Tue, 30 Apr 2024 01:28:15 GMT

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

Re: Mapping inside of Power Query

dufoq3 — Tue, 30 Apr 2024 04:42:10 GMT

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 😎.

Re: Mapping inside of Power Query

irfan_abdrhman — Tue, 30 Apr 2024 07:34:35 GMT

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.

Re: Mapping inside of Power Query

dufoq3 — Tue, 30 Apr 2024 07:46:08 GMT

This has to be SSI not just SS

Re: Mapping inside of Power Query

irfan_abdrhman — Tue, 30 Apr 2024 07:53:37 GMT

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

Re: Mapping inside of Power Query

dufoq3 — Tue, 30 Apr 2024 09:04:58 GMT

You're welcome, enjoy 😉

Re: Mapping inside of Power Query

irfan_abdrhman — Thu, 02 May 2024 01:43:42 GMT

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

Re: Mapping inside of Power Query

dufoq3 — Thu, 02 May 2024 07:24:32 GMT

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).