Forum Discussion
Generate Part Number Configurations Using Power Query
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.
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.
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 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
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.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
