Need Assistance Combining Row Data

[mikekolba]

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

[v-danhe-msft]

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

[mikekolba]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[mikekolba]

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.

[mikekolba]

Thanks Daniel.  I'll take a look and see if it does what i need and let you know.

Mike