Log In
Register
Home
Active Member
November 13, 2023
Offline
Dear PQ specialists
I would like to create BOM's for machines that we manufacture in a more structured way using PowerQuery. For this I have downloaded complete BOM's from SAP (to EXCEL). The BOM can contain up to 20000 lines (assemblies & single parts). The BOM structure can consist of up to 10 levels. Level 1 refers to the machine, level 2-10 to the main and sub-assemblies and individual parts. Only 3 levels are shown in the Excel upload. I have already solved the whole issue with Visual Basic (macro), but I wonder if this is also possible with Power Query and the use of loops... I have learned that loops are not easy to program with PQ. I want to run through an If than else loop using an index, which copies the text of the assembly into the fields with X until the next appearance of a new assembly. In the final stage I want to automatically recognize the number (up to 10) of levels and create new columns accordingly. All help is welcome, maybe the conversion only works with Visual Basic.
Best regards
Helmut
See Answer
Moderators
January 31, 2022
Online
I looked at your query and it seems that you are creating new columns "Level1", "Level2" etc based on the 'Stufe' columns where you replaced "X" with null with an if .... then .... else formula.
It's easier to select all columns that contain the X's (that would be Stufe1 through Stufe9 if I understood correctly). Then right-click on a header of any of the selected columns, Replace values... and then find X replace with null
When that is done you can right-click a header again (keep the same columns selected) and Fill, Down.
The attached file contains your file with a query that does what I described above.
Not sure what you want to do from then onwards, though. Perhaps you can elaborate a bit.
Active Member
November 13, 2023
Offline
Hi Riny, thanks for the quick reply. Perhaps I have described my problem incorrectly. Whether I replace "null" or "X" is generally irrelevant. I want to run through all rows in PQ of the BOM (loop via index 1-139) and, for example, fill the empty fields (or X) below "Components Magnetron" in the Level2 column with "Components Magnetron" up to "Module T0 compl".
Then do the same for "Module T0 compl"
In simpler terms:
Index2: Copy "Components magnetron"
Index3-5: Replace "null" with "Components magnetron"
Index6: Copy "Module T0 compl."
Index7-11: Replace "null" with "Module T0 compl."
etc. until index 139 is reached.
In general: How do I run through a list with the index 0...n and include an IF...Than...Else query based on the content of a field or a calculation.
There are functions like List.Accumulate etc, but I don't know if they solve my problem.
Maybe there is a very simple approach that I haven't realized yet.
Regards
Helmut
Moderators
January 31, 2022
Online
Isn't that what I did? Perhaps I just don't understand, but there is no need to loop through all rows.
For example in 'Stufe2', index 2 has "Komponenten Magnetron". then three rows with "X" and row 6 has "Modul T0 kpl." followed by some X's and another description. Replacing the X's with null and then Fill Down, PQ automatically copies the descriptions down to fill the nulls.
Answers Post
Active Member
November 13, 2023
Offline
Hi Riny , you made my day :).
Maybe I didn't have enough coffee, but I blindly ignored your solution, my mistake (senile stubbornness) .
Of course it works wonderfully and runs lightning fast. Since the number of levels can be up to 10 I have to create many new conditional columns for "object short text/Objektkurztext" and the "Component number/Komponentennummer". The reason for this is that only the "Component number" uniquely describes the component, but not the "Objektkurztext". I only need the "Object short text" to search for parts. However, I will not create all 10 levels, but 6 with 12 new conditional columns: Level1, Componentno.1,Level2, Componentno.2 etc.
Thanks a lot für you support. Using PQ seems to be much faster and easier than visual basic!
Helmut
1 Guest(s)
