Partitioning

Question

Partitioning

Matthew Boustead 40

Looking at analysing the cost of our business with a report from Sage. We get a list similar to below:

PRADEEPCHEEKATLA 91,866 Reputation points

2023-12-05T04:45:47.0533333+00:00

@Matthew Boustead - Thanks for the question and using MS Q&A platform.

Could you please confirm which Azure service are you using?
Matthew Boustead 40 Reputation points

2023-12-05T07:57:05.6866667+00:00

Apologies, it is Azure Synapse Analytics @PRADEEPCHEEKATLA
PRADEEPCHEEKATLA 91,866 Reputation points

2023-12-07T07:15:46.8566667+00:00

@Matthew Boustead - Thanks for the confirmation.

It seems like you are looking for a way to link the project code to each job in your report. Partitioning can be a useful technique to improve query performance, but it may not be the best solution for your specific need.

To clarify, are you trying to link each project code to a specific job within that project? If so, you may want to consider adding a column to your report that includes the job code or ID. This would allow you to easily link each project code to its corresponding jobs.

Alternatively, if you have access to the underlying data source, you could modify the query used to generate the report to include the job code or ID as a separate column. This would eliminate the need to modify the report itself.

Let me know if this helps or if you have any further questions!
Matthew Boustead 40 Reputation points

2023-12-07T08:08:12.7666667+00:00
It doesn't look like the list has been uploaded, see below

<Project Code>
<MAT>

<LAB> <1A> <1B>

<Project Code>

<MAT>

...

Then the next adjacent to this is a 'cost' column but in an ideal world I'd get:

<MAT> <LAB> <1A> ....

<Project Code>

<Project Code>

With the gaps being the cost values
PRADEEPCHEEKATLA 91,866 Reputation points

2023-12-11T03:26:32.4633333+00:00

@Matthew Boustead - Could you please share more details on the scenario along with your requirement?
Matthew Boustead 40 Reputation points

2023-12-11T08:16:09.7433333+00:00
I currently have a list of the costs throughout the business on a monthly basis, pulled from Sage 200 reports.

< Job Name > <Actual Costs>

<Overall Costs>

<Cost A>

<Cost B>

<New Job Name>

<Overall Costs> <Cost A> <Cost B>

and so on.

I am wanting to move this column into a row header and then only have the "Job Name" in the first column and the costs associated to the job in the header rows above and the values to fill in accordingly, something like this below:

<Actual Costs> <Job Name> <Overall Costs> <Cost A> <Cost B>

I hope this helps
PRADEEPCHEEKATLA 91,866 Reputation points

2023-12-13T02:48:14.3433333+00:00
@Matthew Boustead - It sounds like you want to pivot your data from rows to columns using Azure Synapse Analytics. You can use the Pivot transformation in mapping data flow to achieve this.

To pivot your data, you need to select the group by columns, the pivot key, and how to generate the pivoted columns. The group by columns are the columns that you want to aggregate the pivoted columns over. The pivot key is the column whose row values get pivoted into new columns. By default, the pivot transformation will create a new column for each unique row value.

Here's an example of how you can use the Pivot transformation to pivot your data:

In your mapping data flow, add a Pivot transformation.

In the Pivot transformation, select the "Job Name" column as the pivot key.

Select the "Actual Costs", "Overall Costs", "Cost A", and "Cost B" columns as the group by columns.

Run the data flow.

This should pivot your data so that the "Job Name" column becomes the row header and the "Actual Costs", "Overall Costs", "Cost A", and "Cost B" columns become the header rows above. The values should fill in accordingly.

I hope this helps! Let me know if you have any other questions.
Matthew Boustead 40 Reputation points

2023-12-13T09:27:12.4866667+00:00

Thank you for your reply, my concern is that I have my "Job Name", "Overall Costs", "Cost A" are all under the same column name. Then the column next to them is "Actual Costs", I am wanting to pivot the column with "Job Name", "Overall Costs", "Cost A".

I have attached a a picture with an example of my situation, currently Azure can't differentiate the different costs for each job and when I upload to Power BI it will just group each cost, not link each cost to the job it is under. (I have coloured over the job number and location)
Matthew Boustead 40 Reputation points

2023-12-19T15:29:36.85+00:00

@PRADEEPCHEEKATLA any luck?
PRADEEPCHEEKATLA 91,866 Reputation points

2023-12-20T05:10:53.2766667+00:00

@Matthew Boustead - We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.

In case if you need immediate assistance, if you have a support plan could you please file a support ticket for deeper investigation and do share the SR# with us?