Nov 11 2021 12:41 AM
Nov 11 2021 12:41 AM
I have some challenging issue in excel .
I receive format file from someone who can't change his format to my demands .
I am receiving from him a file with activities done by his employees and I need to merge it to my DB .
The problem is : he enters each task in different columns as you can see in the attached file-Original sheet.
I need to receive it by lines : each task which suits the name emp/zone/city/street should get a new line and indicate the quantity.
I tried to explain everything in the file and it be great if someone could help me with that issue
Nov 11 2021 03:54 AM
Nov 11 2021 04:06 AM
@Isra9632 May I suggest that you start learning about PowerQuery first. The link below is a good starting point. Then, you will be able to follow the steps I applied.
Nov 11 2021 06:49 AM
Power Query is the software tool that is built to perform ETL tasks such as you require.
Also, your subcontractor has a reasonable case to build the data as they do, in the form of a cross-tab. They need a readable form, which can be prepared without excessive double entry of descriptive text fields. You have a different need, that is to process the data or upload it to a BB.
I would observe that Excel 365 is also capable of performing tasks of this nature and, with the latest versions, the tedious steps can be hidden away within a named Lambda function. For example
where the Lambda function refers to
= LAMBDA(txt,hdr,d, LET( n₀, 1+COLUMNS(txt), n₁, ROWS(d), n₂, COLUMNS(d), k, SEQUENCE(n₁*n₂,,0), h, SEQUENCE(1,1+n₀), r, 1+QUOTIENT(k, n₂), c, 1+MOD(k,n₂), q, INDEX(d, r, c), a, LEFT(INDEX(hdr, c),5), t, IFS(h<n₀, INDEX(txt, r, h),h=n₀, a, 1, q), FILTER(t, ISNUMBER(q)) ) )
It is all rather tedious, but the Lambda function makes it reusable.
Nov 11 2021 07:31 AM
As a comment.
That is long story and never solved discussion what is better to use, Power Query or Dynamic arrays. If we consider modern Excel, legacy formulae also work perfect in some cases.
@Riny_van_Eekelen prefers Power Query. Understandable, PQ provides rich functionality for data transformations, even if don't use M-coding.
@Peter Bartholomew suggests great sample of using dynamic arrays, sometimes taking into account future Excel functionality which is available only in Beta now.
Both works. What to use depends on goals, but quite often we don't know the goals. We are asked concrete question how to generate this or that data set. But we don't know how it will be used. Do we need filtering / sorting? How this data set will be used in other calculations? Shall it show changes immediately or Refresh All is acceptable option for end users? Etc., etc.
Next we have Power BI datasets, Dataverse, Excel for web specific, whatever. We know nothing about environment, concrete details of use, end users skills and future plans for integration.
Thursday evening thoughts, sorry...