Flattening tree records

[alexcortbawi]

I am new to power query and am trying to solve a problem for a friend. Any help would be highly appreciated. I have a folder containing text files generated by an old sybase database that contain pertinent component proportions for each batch produced including batch number and date of production (See ExcelguruquerytxtFile). I have been able to reach half way through the shaping of the data but have not been able to flatten the required parameters to generate pertinent pivottables.

Thanks in advance for your help.

 

[Dan Bliss]

You need to isolate the header values that contain the batch nbr, date and recipe. Repeat the following for batch and recipe, using date as an example:

1. Make a copy of Column2 (column with date in it),
2. Change type of Column2 - Copy to text (yes text, not date or date-time),

3. Highlight/Select Column2 - Copy, on the transform ribbon, in the Text Column area, select the dropdown, Extract and choose length,
("Column2 - Copy" will transform into values showing the length of what is displayed in Column2!)

4. Add a conditional custom column: if "Column2 - Copy" "is more than" (value = ) 6 then (Output = ) Column2, Otherwise "null"
(The value 6 can be changed to whatever you think will isolate the date value from all others.
(Be sure the "Otherwise" value is null, otherwise the "fill down" step (Step 5) will not work.)

5. Highlight/Select "Custom" column, on the transform ribbon, fill down,
6. Rename to Date field,
7. Change type to Date-time
(All the KOP1 row values in custom will be wrong, but don't worry...

Repeat Steps 1-7 for Date, Batch & Recipe
Then highlight Column1, and de-select all the KOP rows.
The remaining MVCH rows will have be "flattened" and will have columns for with the correct Date, Batch & recipe.

Let me know if this works for you or not.

 

[alexcortbawi]

I added a copy of a text file for easier access

 

[alexcortbawi]

Thank you so much for your prompt reply. I followed steps 1, 2, and 3 as informed. I changed the syntax of step 4 to remove errors and got a column with the proper values where the logical command applied and null otherwise. Unfortunately even after changing type from any to text neither fill down nor fill up work.

I appreciate your help.

 

[Dan Bliss]

Alex,
I loaded lieferschein_104061.txt; to duplicate your difficulties. It seems the file whitespace is not {space}, but perhaps {tab} or some other non-printable character. Replacing non-printables requires some more work. Still working on this and will get back to you soon.
Dan

 

[Dan Bliss]

Alex,

Since I didn't see a response, my guess you have moved on to the next problem. Still let me tell you what I found and possible solutions.
1. It matters how you loaded these files into Excel. If you loaded the data into an Excel sheet, you may have noticed that Excel sensed that the delimiters were TABs. Once in an Excel sheet, you can highlight the TOP-LEFT/RIGHT-BOTTOM cells, convert to table, then load the table into a Query. But it is simpler to load from txt file directly into a Query:
Click on New Query, select From File in dropdown list, then choose from CSV. Since your files are .txt, you need to change the extension type in the Import Data window to see them. Once selected, click edit to take you to the Query UI. There are only 2 columns, column2 having the bulk of the data.

THE BIG TIP:
1. Click on Column2, Transform Tab, Click "Replace Values"
2. In the replace values window, type #(tab) in "Value To Find" box. In the "Replace With" box, put one comma, (or whatever delimiter you prefer). Be sure the formular bar is showing (If formula bar is not showing click Formula Bar box under View tab)
3. Make sure the red underlined portion of following expression is what shows on your formula bar. Change if different:
= Table.ReplaceValue(#"Changed Type","#(tab)",",",Replacer.ReplaceText,{"Column2"})

4. Notice that in Column2 all whitespace has been replaced with commas (or your specified delimiter).
Now you're ready to split Column2 into several pieces easily.
5. Under the Transform tab, click "Split Column" and choose "By Delimiter" with Column2 highlighted.
6. I suggest you choose radio button, "at the left-most delimiter", until you get familiar with the results.

Handling txt or CSV files in this manner may be easier loading multiple files.

Hope this helps,
Dan

 

[alexcortbawi]

Hi Dan,

The only reason you didn't get a response is because I was sleeping. I suppose we are in different time zones that's all. I really appreciate your replies for I still haven't solved this nutcracker and was trying to assimilate the different m language possibilities before I fell asleep.
Here is my feedback:

The text file lieferschein_104061.txt is actually a lieferschein_104061.cim file. Lieferschein means Delivery Voucher in German and the number after gives you an idea of how many text files there are. All these files are loaded in one folder and the easiest way to take advantage of power query is to query the whole folder. I have uploaded one file to simplify things.

I don't have to change the extension as excel senses that it is a csv or text file. I tried changing extensions and it makes no difference. As you said the bulk of the data is in one column. Instead of replacing tabs by commas I just used the delimiter to split the columns by Tab and got the results I presented in the second picture I downloaded so this part is already solved. Your first reply was more helpful and I am trying to take it from there. The fill down step is not working and I am trying to learn the language to see if there is a way around it. Will let you know regardless if i find a way or not. I hope you do the same still I wish to thank you for you have perhaps unintentionally given me several leads which may allow me to solve the problem.

Thanks again Dan

 

[alexcortbawi]

PROBLEM SOLVED
Your initial message was correct. I just fiddled a bit with the conditional column and changed the logical sentence to something easy and the fill down function worked like a charm. No need to create a duplicate column. Just select one of the existing columns add a logical sentence refer to a number or a name. If the logical sentence is true then return value of same or other column of choice. else i typed null as you suggested. When the new column appeared i filled down and voila.

 

[Dan Bliss]

Glad I could help