Challenging issue

[Isra9632]

Hi,
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
Thx

[#2]

@Isra9632 Easily done with PowerQuery. See attached, the orange table on the "After Change" sheet. Note that I couldn't reproduce item number 6 from your table. And I don't understand where the Type column should come from.

[#3]

Thanks a lot Riny . Could you refer me pls to the whole solution resolved by power query . I tried but didn't succeeded to execute this.
Thx again Isra

[#4]

@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.

https://exceloffthegrid.com/power-query-introduction/ 

[#5]

@Isra9632 

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 

= UNPIVOTλ(descript,item,qty)

would generate

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.

[#6]

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...