Unpivoting groups of changing number of columns

Power Query
1

Hi Sam,
I’m facing issues with a pivoted report from GitHub I need to arrange differently.

The report lists for each repository a list of one or more admins where each admin has a status and a login, but may have one or more emails.
The dynamic number of admins and admins’ emails puts this transformation out of my league.
I made a naive attempt but still, I cannot get what I need. I still have status, login and emails on 3 different columns.

image
image785×132 5.3 KB

I’m adding a sample Excel with my attempt, representing a small but meaningful excerpt of the real data.

Thanks for the support.

sample.xlsx (28.5 KB)

sample.pbix (30.6 KB)

2

Hi @Roberto.

Please upload your work-in-progress PBIX along with a marked-up screenshot and/or Excel mockup of the desired outcome visual that you’re having difficulty with, for the forum members to review.

Greg

3

Hi @Greg, I added to my post a pbix file replicating the same logic.
Thanks for your prompt reply

Roberto

4

Hello @Roberto,

Try this code. You need to change the Source to open your local excel file

let
    Source = Excel.Workbook(File.Contents("C:\Users\abadiu\Downloads\sample.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Table1_Table, {"Organization", "Repo Name", "Open findings", "type"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "login", each if Text.Contains([Attribute], "login") then [Value] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "status", each if Text.Contains([Attribute], "active") then [Value] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "email", each if Text.Contains([Attribute], "email") then [Value] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column2",{"Attribute", "Value"}),
    #"Added Conditional Column3" = Table.AddColumn(#"Removed Columns", "Flag", each if [status] <> null then 1 else if [login] <> null then 1 else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column3",{"login", "status"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"email"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([status] <> null)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows"),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Duplicates", each ([Flag] = null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Flag"})
in
    #"Removed Columns1"

image
image1127×414 98.7 KB

3 Likes
5

Thanks @alexbadiu, it worked! The fill up/down is what I had missed.