cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Pivot to hardcoded # of columns - I have 2 rows but want 3 columns

 

I'm pivoting the Id's to display the urls in columns vs rows. My ultimate goal is to pivot and rename the pivoted urls columns to Photo1,Photo2,Photo3. Notice the 3rd row does not exist for id#89 but I always want 3 pivoted columns to be generated. I expect 0 to 3 photos for each record.

Before

briguin_0-1606510422610.png

After: Results work as expect

briguin_1-1606510522514.png

 

Is there a way to hardcode the pivot to 3 columns vs using the rows index? Could I adjust the MCode and manipulate the list?

 

= Table.Pivot(Table.TransformColumnTypes(#"Replaced Value", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Replaced Value", {{"Index", type text}}, "en-US")[Index]), "Index", "URL_Attachment_FullPath")

 

Here are some ideas I had:

#1) Change the test data to 3 records. If the real data doesn't have a record with 3 photos the naming step will fail because a 1,2 or 3 pivoted column will be missing.

#2) Add a temp table with 3 records, append to the unpivoted data, pivot and rename columns. Once complete filter out the temp record. This solution did work but seems like there should be a better way


Edit: My Idea #2 did work as a solution but still think there should be a better way

1 ACCEPTED SOLUTION
Super User III
Super User III

@briguin ,

Marque este Mcode:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTKzsNDLKkhXitWJVjIC8o2cUfmm5kZwvjGQb4jCQ5UzhvNMgDyl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, URL = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"URL", type text}}),
    #"Grouped Rows" = Table.Group(
                                    #"Changed Type", {"ID"}, 
                                    {
                                        {"Rows",
                                            each  
                                                let _tb = _ in 
                                                    Table.AddIndexColumn(_tb, "Index", 1,1) &
                                                    Table.FromList(
                                                        List.Transform(
                                                            List.Numbers(
                                                                Table.RowCount(_tb) + 1,
                                                                3 - Table.RowCount(_tb)
                                                            ), each Number.ToText(_)
                                                    ), 
                                                    Splitter.SplitByNothing(), 
                                                    {"Index"}
                                                    ),
                                            type table
                                        }
                                    }
                                ),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"URL", "Index"}, {"URL", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Rows", {{"Index", type text}}, "pt-BR"), List.Distinct(Table.TransformColumnTypes(#"Expanded Rows", {{"Index", type text}}, "pt-BR")[Index]), "Index", "URL"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"ID", Int64.Type}, {"1", type text}, {"2", type text}, {"3", type text}})
in
    #"Changed Type1"

Capture.PNG

Also, check the attachment.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

1 REPLY 1
Super User III
Super User III

@briguin ,

Marque este Mcode:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTKzsNDLKkhXitWJVjIC8o2cUfmm5kZwvjGQb4jCQ5UzhvNMgDyl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, URL = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"URL", type text}}),
    #"Grouped Rows" = Table.Group(
                                    #"Changed Type", {"ID"}, 
                                    {
                                        {"Rows",
                                            each  
                                                let _tb = _ in 
                                                    Table.AddIndexColumn(_tb, "Index", 1,1) &
                                                    Table.FromList(
                                                        List.Transform(
                                                            List.Numbers(
                                                                Table.RowCount(_tb) + 1,
                                                                3 - Table.RowCount(_tb)
                                                            ), each Number.ToText(_)
                                                    ), 
                                                    Splitter.SplitByNothing(), 
                                                    {"Index"}
                                                    ),
                                            type table
                                        }
                                    }
                                ),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"URL", "Index"}, {"URL", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Rows", {{"Index", type text}}, "pt-BR"), List.Distinct(Table.TransformColumnTypes(#"Expanded Rows", {{"Index", type text}}, "pt-BR")[Index]), "Index", "URL"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"ID", Int64.Type}, {"1", type text}, {"2", type text}, {"3", type text}})
in
    #"Changed Type1"

Capture.PNG

Also, check the attachment.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors