Rows in columns

[Tinus1905]

Hi,

I want rows into columns in PowerBi query editor. 

From this:

Into this:

Is that possible? 

[Anonymous · Accepted solution]

Hi @Tinus1905 ,
Here are the steps to resolve.
1. Use "Group by" transformation to remove duplicate combination.
2. Again use "Group By" transformation and add an Index column.
3. Create a custom column by using the Index number such as "IP1", "IP2" etc.
4. Use pivot to get the resultset.   

Below are the Power Query transformation steps you can check. Replace the source as per your code

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRNTDTNTIwMlbSUQpILUktUvBKTM4uzs8D8g2N9IxNTPXAlFKszgCqNjEy1DM01DMxNdMzh6g2RVLtk5mXkqjgkl+Oy2g8io1NLPQsLcGKjUlUHAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, IP = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"IP", type text}}),
#"Grouped Rows-GetMaxIP" = Table.Group(#"Changed Type", {"Date", "Name", "IP"}, {{"MaxIP", each List.Max([IP]), type nullable text}}),
#"Removed Columns-Duplicates" = Table.RemoveColumns(#"Grouped Rows-GetMaxIP",{"IP"}),
#"Renamed Columns-IP" = Table.RenameColumns(#"Removed Columns-Duplicates",{{"MaxIP", "IP"}}),
#"Grouped Rows-GetIndex" = Table.Group(#"Renamed Columns-IP", {"Name", "Date"}, {{"IndexTbl", each _, type table [Date=nullable date, Name=nullable text, IP=nullable text]}}),
#"Added Custom-IndexColumn" = Table.AddColumn(#"Grouped Rows-GetIndex", "Index", each Table.AddIndexColumn([IndexTbl],"Index",1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom-IndexColumn",{"Index"}),
#"Expanded Index" = Table.ExpandTableColumn(#"Removed Other Columns", "Index", {"Date", "Name", "IP", "Index"}, {"Date", "Name", "IP", "Index.1"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Index", "IPIndex", each "IP" & Number.ToText([Index.1])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index.1"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[IPIndex]), "IPIndex", "IP")
in
#"Pivoted Column"

[Anonymous · Accepted solution]

Hi @Tinus1905 ,
Here are the steps to resolve.
1. Use "Group by" transformation to remove duplicate combination.
2. Again use "Group By" transformation and add an Index column.
3. Create a custom column by using the Index number such as "IP1", "IP2" etc.
4. Use pivot to get the resultset.   

Below are the Power Query transformation steps you can check. Replace the source as per your code

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRNTDTNTIwMlbSUQpILUktUvBKTM4uzs8D8g2N9IxNTPXAlFKszgCqNjEy1DM01DMxNdMzh6g2RVLtk5mXkqjgkl+Oy2g8io1NLPQsLcGKjUlUHAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, IP = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"IP", type text}}),
#"Grouped Rows-GetMaxIP" = Table.Group(#"Changed Type", {"Date", "Name", "IP"}, {{"MaxIP", each List.Max([IP]), type nullable text}}),
#"Removed Columns-Duplicates" = Table.RemoveColumns(#"Grouped Rows-GetMaxIP",{"IP"}),
#"Renamed Columns-IP" = Table.RenameColumns(#"Removed Columns-Duplicates",{{"MaxIP", "IP"}}),
#"Grouped Rows-GetIndex" = Table.Group(#"Renamed Columns-IP", {"Name", "Date"}, {{"IndexTbl", each _, type table [Date=nullable date, Name=nullable text, IP=nullable text]}}),
#"Added Custom-IndexColumn" = Table.AddColumn(#"Grouped Rows-GetIndex", "Index", each Table.AddIndexColumn([IndexTbl],"Index",1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom-IndexColumn",{"Index"}),
#"Expanded Index" = Table.ExpandTableColumn(#"Removed Other Columns", "Index", {"Date", "Name", "IP", "Index"}, {"Date", "Name", "IP", "Index.1"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Index", "IPIndex", each "IP" & Number.ToText([Index.1])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index.1"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[IPIndex]), "IPIndex", "IP")
in
#"Pivoted Column"

[Tinus1905 · Accepted solution]

@Anonymous ; this works for me, but now I have another table with 180 different IP numbers. 

So now I change all the IP numbers in 2 words; "Home" and "Office". 

How do I use the formula on this?

[Anonymous]

Hi,

If your other table containing list if IPs and categoriesed as Home/Office, then you can merge your primary data table with the mapping table and then follow the pivot/unpivit functionalities to get the desired outcome.

Hope this will address your requirement.

Thanks,

Santo

[some_bih]

Hi @Tinus1905  check link for un/pivot columns