Create special table format with grouping from multiple rows

[gaborocsai]

Hi All,

I am lost with finding automatic solution for that one:

Attached sheet, tab 1: the export is a csv file, contains multiple rows, but containing elements which would be the header of a brand new sheet.
Tab 2: needed format, data from tab 1, grouping the first 2 columns; column C to column J would be the data from tab 1, column C. The values would be data from column D from tab 1, related to the new header.

Generally, export file would be thousand of rows, and I need this grouped special format.

Any idea? Where to start?

Thanks in advance!

[AliGW]

Welcome to the forum.

Easy with PowerQuery.

M Code:

Please Login or Register  to view this content.

[gaborocsai]

Hi Ali,
Seems good. How to resolve this?
"DataFormat.Error: We couldn't convert to Number."
So value is Error where there are numbers.

[AliGW]

That's not the data you've provided. Provide an updated sample workbook with realistic sample data!

[gaborocsai]

Here you have it. Please check with it.

Plus one question: How could it solve not to run always a power query when new data is added (copied) to the table.
If I add manually, then new columns appear on the new tab.

[JeteMc]

Try the following advanced editor code (M Code):

Please Login or Register  to view this content.

Note that the last row of data was added after the table on Sheet1 was produced.
To add the "NEW" data I:
1. Selected the Query Tools tab
2. Selected Refresh
Let us know if you have any questions.

[gaborocsai]

Hi there again,

None of the 2 advice worked for me.
Please see some addon to the problem.
1. The export files are always different.
2. Grouping is now not needed as only the first column remained in the sheet.
3. There could be different number of rows. Column 2 could contain different values, and these values are not always related to column 1.
4. Based on that one query is required, which could be easily inserted, no matter whether file is like the attached 1st or the 2nd.
5. The new table requirement is the same as wrote earlier:
First column: only one data from the source first column.
From second till X column: the data from the source second column. (They will be the headers.)
Last column: the data from the source thirs column. (Related data to the headers above.)

Attached you can see 2 simple sample example.

Could you let me know whether it can be solved and how with PowerQuery?

Thanks,
Gabor

[AliGW]

Please provide a manual mock-up of what you want the combined data to look like once processed, as your requirements have clearly changed.

[gaborocsai]

Please see attached.

[AliGW]

Why two files???

[AliGW]

This PQ M Code works on both your files:

Please Login or Register  to view this content.

[gaborocsai]

What happens when there are more than 2-3-4 new columns are?
So id SB Total Weekend WK WBH, but there are column 5, column 6,...

[AliGW]

Try it and see!!!

I can work ONLY with what you give me. If what you have provided is not what you are working with, then you are wasting my time and yours.

[gaborocsai]

Could you please check with this one? This is one of my source file.
It gave error for me.
Thanks in advance.

[gaborocsai]

These were what I work with, but I also sent the full file combined the differrent parts together. Sorry if I was not clear earlier.

[AliGW]

The final record in that set does not match the other rows, so it won't work. Is that an error, or something that you have to work with?

Excel 2016 (Windows) 32 bit

	A	B
383	009031; WK; 2.00
384	009062; CM; 7.00
385	009062; L; 6.00
386	009062; SB; 3.00
387	009062; Total; 131.77
388	009062; Weekend; 22.93
389	009062; WK; 3.00
390	AK; Total; 11.00	Problem record

Sheet: Tamigo-CsvExport_20201201-20201

[gaborocsai]

Oh... this record could be deleted. Sorry I did not go through the whole file.
I attached the final one.
And this is the error message:
"Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]"

[AliGW]

No, it works fine, and I've added a second version of the query to the attachment that has the final Changed Type line removed - both options work.

Now attached!

[gaborocsai]

Could you please send the final file you got?

[AliGW]

It's attached above.

[gaborocsai]

Thank you so much.
Maybe your Excel is a newer one and PQ might be a different version. That is why it's not loaded correctly.
I try to figure out what is the problem.

[AliGW]

I doubt that - the query is using basic functions that have been in all versions I've used - nothing new.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[gaborocsai]

Hi Ali and All,
I have just realized, that in the file you sent, the query results 3 errors.
In the new pivot, in row 6, 27 and 51 some columns not counted (Total, WBH, Weekend).
The reason: there are multiple data for these "ID"-s.
1. 003235 - Total (A27,A28), 003235 - Weekend (A30,A31)
2. 004916 - Total (A143,A144), 004916 - Weekend (A146,A147)
3. 008732 - Total (A290,A291), 008732 - WBH (A292,A293), 008732 - Weekend (A294,A295)

Is it possible to summarize the coherent data?

[AliGW]

I am at work today, so won't be able to assist, however I strongly suggest that you show us what you want the resulting data to look like where there are multiple values. I think it may require grouping before pivoting, but give us something to work with.

It's extremely frustrating when members provide a working solution only to be told that the real data isn't like the data you've given them to work with. This thread is now getting quite long, and unnecessarily so, so SHOW US WHAT YOU WANT!

[AliGW]

Here's a version with grouping (sum aggregation). See if you can work with it.

M Code:

Please Login or Register  to view this content.

[gaborocsai]

Hi,
I am not experienced neither in Excel, nor in how to explain problem to experienced Excel users.
The latest one seems perfect for me. I checked the final pivot manually and it looks like all data is correct in it.
Sorry that I was not able to communicate clearly what was my problem, I did not want to make anybody extremely frustrated.
Thanks at all, and take care.

[AliGW]

You don't need to be experienced in Excel to explain what you are trying to do, with a start and end point. These are things you KNOW, so need to articulate them clearly.

I'm glad it worked. Please remember to mark the thread as solved.