topic Re: Generate Part Number Configurations Using Power Query in Power Query

Generate Part Number Configurations Using Power Query

TessMissy — Sun, 22 Jun 2025 22:47:13 GMT

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

Re: Generate Part Number Configurations Using Power Query

lbendlin — Tue, 24 Jun 2025 00:49:25 GMT

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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Generate Part Number Configurations Using Power Query

V-yubandi-msft — Tue, 24 Jun 2025 07:17:27 GMT

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:

A sample of the input data in table format (not as screenshots)
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.

Re: Generate Part Number Configurations Using Power Query

TessMissy — Wed, 25 Jun 2025 02:51:00 GMT

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

Re: Generate Part Number Configurations Using Power Query

TessMissy — Wed, 25 Jun 2025 03:06:09 GMT

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

Re: Generate Part Number Configurations Using Power Query

V-yubandi-msft — Thu, 26 Jun 2025 07:36:32 GMT

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.

Re: Generate Part Number Configurations Using Power Query

TessMissy — Mon, 30 Jun 2025 01:04:29 GMT

Hi,

Thanks for your interest and resposne to my query. If I'm following you're approach correctly the configuration list only generates a table for No. 0 washers? So another table is required for No. 2 etc.

I'm trying to generate one table of all the combinations of configurations.

For example,

No. 0 - GN016P etc.

No. 2 - GN216P etc.

No. 3 - GN316P etc.

Is this possible?

Thanks

Re: Generate Part Number Configurations Using Power Query

v-kpoloju-msft — Mon, 07 Jul 2025 05:25:52 GMT

Hi TessMissy,

Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario again, and it worked on my end. I used my sample data and successfully implemented it.

Outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.