Generate Part Number Configurations Using Power Query

[TessMissy]

Good Morning,

I am new to PQ and have a request to those out there with more experience using Power Query. I am requesting advice and help to generate configuration part numbers using PQ.

Using the standard NAS1149 (Flat Washers) as an example, the part number generated follows this format:

I am requesting advice on the most efficent way to generate configuration part numbers in PQ from this Standard.

Any advice or suggestions appreciated.

Thanks

[V-yubandi-msft]

Hi @TessMissy ,

Thanks for posting your query in the Microsoft Fabric Community.  I tried your NAS1149 part number scenario in Power Query and found a solution that matches the format you need.

Please take a look and let me know if it works for you or if you want any changes.

FYI:

Used Code:

Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(
    List.Select({
        Text.From([BasicPartNumber]),
        Text.From([MaterialCode]),
        Text.From([SizeCode]),
        Text.From([OversizeCode]),
        Text.From([FinishCode])
    }, each _ <> null and _ <> ""),
    " "
))

For future questions, it would be helpful if you could provide:

1. A sample of the input data in table format (not as screenshots)
2. An example of the desired output based on that input

This helps ensure we can support your request more accurately. If the solution works, marking it as accepted could also assist others with similar issues.

[TessMissy]

Hi, thanks for reaching out.

What I'm attempting do is create a list of NAS1149 configurations following the naming convention detailed in the Standard. So far I have developed several queries but am struggling to put it all together.

For example I have developed a query which lists Material Codes and Finish Codes. This has been created from NAS1149 Table II.

MATERIAL CODE	FINISH CODE
G	P
G	B
G	F
D	H
D	K
D	K2
D	J
D	J2
A	H
A	K
A	K2
A	J
A	J2
B	H
F	B
F	P
F	F
C	B
C	R
E	P
E	R
E	R2
E	R3
E	W
E	U
E	F
T	H
T	L
V	H
V	M
V	W

I have also developed a qeury which list the Size and Thickness for each configuration. For example nominal diameter NO. 0 comes in two thicknesess 0.016" and 0.032", named N016 and N032 respectively.

NO. 0

N016

N032

I have devloped an output query which is a single column listing the various configurations for NO. 0 which is an amalgamation of the two tables shown above. I am trying to develop one table or column of configurations listing Material Code, Thickness, and Finish Code for each nominal diameter i.e., NO. 0, NO. 2. etc. 

The following table shows some configurations fo nominal diameter NO. 0. Thanks

NO. 0

GN016P

GN016B

GN016F

DN016H

DN016K

DN016K2

DN016J

DN016J2

[V-yubandi-msft]

Hi @TessMissy ,

Thank you for sharing your scenario. I reviewed your NAS1149 configuration request and developed a flexible solution in Power Query to generate part numbers in the required format.

I began by creating two base tables one for Material Finish combinations (from NAS1149 Table II), and another for Thickness codes (such as N016, N032 for Nominal Diameter No. 0).

To generate all valid combinations, I added a constant column called Key to both tables, enabling a Cartesian join to create all possible pairings.  Once the tables were merged, I used a custom column with this logic

[MaterialCode] & [ThicknessCode] & [FinishCode]

FYI:

This approach dynamically creates part numbers like GN016P, DN032K, and others, based on the selected inputs.

Please let me know if any adjustments are needed.

— Yugandhar
Community Support Team.

[TessMissy]

Continued from the previous post.

I have been able to generate an output for one configuration at one time. I am trying to develop one complete table which identifies the various configurations for every nominal diameter.

Let me know if I need to add or clarify my issue.

Thank you

[lbendlin]

If your reference tables are small enough you may get away with a merge operation based on a common criteria, and a custom column that ties it all together.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...