Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a power bi report where the data is formatted like this
Column1 Column2 Column3
Row1 Data1
Row2 Data2
Row3 Data3
Is there a way in power BI to combine the rows so it looks like this in the report? Note- my data and Row data is based on "source file names) so each row is a different source excel file name. There is certain text that may be identical in the filename but it may not be in the same position and may not be the same length for each file.....
(wish i knew how to embed a picture lol)
Column1 Column2 Column3
Row Data1 Data2 Data3
Mike
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
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
Hi,
You may download my PBI file from here.
Hope this helps.
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.
Thanks Daniel. I'll take a look and see if it does what i need and let you know.
Mike
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |