Forum Discussion
Need Assistance Combining Row Data
Hi mikekolba,
Based on my test, you could refer to below steps:
Sample data:
Create a calculated column:
Table = DISTINCT(SELECTCOLUMNS('Table1',"A",MAX('Table1'[Item]),"B",MAX('Table2'[Item_1]),"C",MAX('Table3'[Item_3])))
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Thanks Daniel. I'll take a look and see if it does what i need and let you know.
Mike
Ok it's a little more complicated so let me try to explain better.
My data looks like this. Each XLSX file has a tab that contains a Column that contains "Rows" named ""Rev and "Profit" with"data in the column next to it (so the first column and it's rows are basically headers)
Source.filename Column1 Column2
ABCD Site New.xlsx Rev 100.00
ABCD Site New.xlsx Profit 50.00
ABCD Site Old.xlsx Rev 75.00
ABCD Site Old.xlsx Profit 10.00
DEFG Site New.xlsx Rev 150.00
DEFG Site New.xlsx Profit 0.00
DEFG Site Old.xlsx Rev 10.00
DEFG Site Old.xlsx Profit 1.00
When i run a report where i have the source.filename as one column, and i create a New REV as another Colum, New Profit as another Column, and Old Rev as another column and Old Profit as a last Column it looks like this
Source.filename New Rev New Profit Old Rev Old Profit
ABCD Site New.xlsx 100.00 50.00
ABCD Site Old.xlsx 75.00 10.00
DEFG Site New.xlsx 150.00 0.00
DEFG Site Old.xlsx 10.00 1.00
DEFG Bob Site New.xlsx 75.00 5.00
DEFG Bob Site Old.xlsx 25.00 2.00
What I'm looking to do is something like this where the 2 rows of data are collapsed together to show the data for the site location like this and you end up with one line of data for a specific site location
Source.Filename (site) New Rev New Profilt Old Rev Old Profit
(or whatever we name this column
if it needs to be a new column)
ABCD Site 100.00 50.00 75.00 10.00
DEFG Site 150.00 0.00 10.00 1.00
DEFG Bob Site 75.00 5.00 25.00 2.00
the filenames are similar within for a site but with certain differences in the filename like i gave in an example above. Also the naming convention for the files from site to site should be identical "for that site" but may not be similar to other sites.
Any ideas on how i could collapse the rows together as illustrated above so it doesn't end up with the emtpy spaces and such (since the files are for a specific Site location, they just vary where one file may be for a certain week of data and another file may be for a different week of data)
Hope that clarifies.
Mike
Thanks. I'll take a look.
One way i did find that wasn't programatic "per-se" but may help me down the road is to set up "groupings" based on these file names. I could then make the "column" based on the group and then the row information would then line up how I desired.
