cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

SharePoint rows into column

Hi All,

 

I have a business requirement to SharePoint column data divided into multiple rows by project.

Example: 

Project NameIT LeadDateText column
ABCDABCD ABCD
XYZXYZ  

 

Now based on the above data i want to create a collection that displays data in the below format.
severity is assigned as per column. So for date if it is blank severity 3 will be provided and if text column is blank then severity should be 5.

Project NameIT LeadColumnSeverity
ABCDABCDDate is blank3
XYZXYZdate is blank3
XYZXYZText is blank5

 

How can I accomplish this?

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @Anonymous :

I assume there is a table:

ClearCollect(
    MyTestTabel,
    {
        'Project Name': "ABCD",
        'IT Lead': "ABCD",
        Date: Blank(),
        'Text column': "ABCD"
    },
    {
        'Project Name': "XYZ",
        'IT Lead': "XYZ",
        Date: Blank(),
        'Text column': Blank()
    }
)

Please add a button and set it's OnSelect property to:

Clear(Result);
ForAll(MyTestTabel,
   If(IsBlank(Date)&&IsBlank('Text column'),
   Collect(Result,{'Project Name':'Project Name','IT Lead':'IT Lead',Column:"Date is blank",Severity:3},{'Project Name':'Project Name','IT Lead':'IT Lead',Column:"Text is blank",Severity:5}),
   IsBlank(Date),
   Collect(Result,{'Project Name':'Project Name','IT Lead':'IT Lead',Column:"Date is blank",Severity:3}),
   IsBlank('Text column'),
   Collect(Result,{'Project Name':'Project Name','IT Lead':'IT Lead',Column:"Text is blank",Severity:5})
  )
)

/*Result is my custom collection to save the result*/

50.gif

Best Regards,

Bof

View solution in original post

12 REPLIES 12
eka24
Super User
Super User

If you are using a Gallery for example, on the Items property:

AddColumns(Products,"Severity",If(TextColumn="",5,If(Dates="",3)))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Anonymous
Not applicable

@eka24 i would want to create a collection with 
Project Name , IT Lead , Column comments , severity.

So specifically, Under column comments it can be date column is empty and its severity. So the severity depends on column comments. 
if the project record includes 10 columns for like date,text etc. , it needs to check for all the columns and create a report like below. I Hope i am making sense?
Final output of collection needs to be 

Project NameIT LeadColumn commentsSeverity
ABCDABCDDate is blank3 (fixed and defined for date)
XYZ XYZdate is blank3
XYZXYZText is blank5 ( fixed and defined for text column)
eka24
Super User
Super User

Under the Column Comments, is "Date is Blank" and "Text is Blank" automatically inserted in your table?

Am asking the question based on:

"if the project record includes 10 columns for like date,text etc. , it needs to check for all the columns and create a report like below"

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Anonymous
Not applicable

@eka24 there will be if criteria for each column. 
Example: if date is blank then Column comments = "Date is blank"

if text is blank then column comments  = "Text is blank"

So the collection will contain 10 different rows for 10 different columns for a single project.
is this doable?

eka24
Super User
Super User

I think you need to Show a sample table of the supposed 10 columns and also show the Output to make it clearer.

The table you have shown does not make it explanatory enough

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Anonymous
Not applicable

@eka24 So the input will be like below which is stored on a SharePoint list.

Project NameUnitFunctionThemeStart DateEnd DateA Start DateA End DateStageStatus
Test ProjectIT Social10/12/202011/12/2020  Started 

The output for the above input from SharePoint List should be:

Project NameIT LeadColumn Commentsseverity
Test ProjectXYZFunction is blank2
Test ProjectXYZA start date is blank5
Test ProjectXYZA end date is blank5
Test ProjectXYZStatus is blank5

I hope this clarifies my requirement?

Create a collection using this:

ClearCollect(
    ProjectCol,
    AddColumns(
        ProjectTable,
        "CommentColumn",
        If(
            Function = "",
            "Function is blank",
            If(
                StartDate = "",
                "StartDate is blank",
                If(
                    EndDate = "",
                    "EndDate is blank",
                    If(
                        Status = "",
                        "Status is blank"
                    )
                )
            )
        ),
        "Severity",
        If(
            Function = "",
            2,
            If(
                StartDate = "",
                3,
                If(
                    EndDate = "",
                    "5",
                    If(
                        Status = "",
                        "5"
                    )
                )
            )
        )
    )
)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Anonymous
Not applicable

@eka24 This creates a record with 2 columns, however I need a collection with records for each column. 
Current output is addition of 2 columns, column comments and Severity however I would want rows to be added for each column check as an individual record for a project in the collection along with severity of that column.

v-bofeng-msft
Community Support
Community Support

Hi @Anonymous :

I assume there is a table:

ClearCollect(
    MyTestTabel,
    {
        'Project Name': "ABCD",
        'IT Lead': "ABCD",
        Date: Blank(),
        'Text column': "ABCD"
    },
    {
        'Project Name': "XYZ",
        'IT Lead': "XYZ",
        Date: Blank(),
        'Text column': Blank()
    }
)

Please add a button and set it's OnSelect property to:

Clear(Result);
ForAll(MyTestTabel,
   If(IsBlank(Date)&&IsBlank('Text column'),
   Collect(Result,{'Project Name':'Project Name','IT Lead':'IT Lead',Column:"Date is blank",Severity:3},{'Project Name':'Project Name','IT Lead':'IT Lead',Column:"Text is blank",Severity:5}),
   IsBlank(Date),
   Collect(Result,{'Project Name':'Project Name','IT Lead':'IT Lead',Column:"Date is blank",Severity:3}),
   IsBlank('Text column'),
   Collect(Result,{'Project Name':'Project Name','IT Lead':'IT Lead',Column:"Text is blank",Severity:5})
  )
)

/*Result is my custom collection to save the result*/

50.gif

Best Regards,

Bof

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,928)