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