Forum Discussion

jaw19883's avatar
jaw19883
Frequent Visitor
5 years ago
Solved

Manipulating Data Source

Hey all - Background: I work in K-12 Ed and am diving into Power Bi. I am working on a project that essentially will create a dashboard for every unit in a course all in one file. 

My problem continues to be the fact that my data source is an exported gradedbook in CSV/Excel. My biggest problem in that is the fact that the LMS includes all units/sections a student is enrolled in, in one field. So several records look like this in my data source: (keep in mind there are many other fields, such as test, quiz, notes, etc.)


Studnet Name      Student ID        Section                        
Student A             9999999            Unit 1 and Unit 2

Student B             1999999            Unit 1, Unit 2, Unit 3

Student C             2999999            Unit 4

 

What I want it to look like is this so I can isolate the Section, then easily apply filters, slicers, etc. based on Unit...

 

Studnet Name      Student ID        Section
Student A             9999999            Unit 1

Student A             9999999            Unit 2

Student B             1999999            Unit 1

Student B             1999999            Unit 2

Student B             1999999            Unit 3

Student C             2999999            Unit 4

 

I would eliminate the Student Name field at this point and create a different table with Name and ID and have a seperate table with ID and Section. Problem is I dont know:

1) Should I do this before I connect the data source to Power Bi or can I do it while transforming the data in Power Bi? AND

2) The syntax/methods to use. I assume it would be some variant of concatenate from Excel with the ability to read the whole string/array and break it up, but it has to bring/duplicate ID with it too. 

 

Here is a screenshot of data with names and Ids changed. Ids are unique and the key

 

Any help, ideas, or holes to go down appreciated. 

 

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi, jaw19883 

     

    According to you description, You could replace and split columns ,then unpivot column. The steps are as follows
    Step1: replace values

    Step2: split column

    Step3: unpivot columns

    Step4: remove columns

    Step5: format trim(Clear the first blank)

    The final output is shown below::

    In addition, M language is as follows:

     

    let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pTUnNK1FwVNJRsoQAICs0L7NEwVAhMS9FAcw0UorVQah1UlAAqjGEqFYAAbgWHah6KG2Mos8ZqMwISRcUwDSbKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Studnet Name" = _t, #"Student ID" = _t, Section = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"and",",",Replacer.ReplaceText,{"Section"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Section", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Section.1", "Section.2", "Section.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Studnet Name", type text}, {"Student ID", Int64.Type}, {"Section.1", type text}, {"Section.2", type text}, {"Section.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Studnet Name", "Student ID"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}})
in
#"Trimmed Text"

     

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi, jaw19883 

     

    According to you description, You could replace and split columns ,then unpivot column. The steps are as follows
    Step1: replace values

    Step2: split column

    Step3: unpivot columns

    Step4: remove columns

    Step5: format trim(Clear the first blank)

    The final output is shown below::

    In addition, M language is as follows:

     

    let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pTUnNK1FwVNJRsoQAICs0L7NEwVAhMS9FAcw0UorVQah1UlAAqjGEqFYAAbgWHah6KG2Mos8ZqMwISRcUwDSbKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Studnet Name" = _t, #"Student ID" = _t, Section = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"and",",",Replacer.ReplaceText,{"Section"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Section", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Section.1", "Section.2", "Section.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Studnet Name", type text}, {"Student ID", Int64.Type}, {"Section.1", type text}, {"Section.2", type text}, {"Section.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Studnet Name", "Student ID"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}})
in
#"Trimmed Text"

     

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.