Combine multiple columns

[jeffrey759]

Hello,

I have multiple Vicinity's with a rating (number), but every Vicinity and rating exists as a new column.
For this, I would like to have: 1 Column with the Vicinity and 1 column with the correct rating.

Example now: 

Vicinity   Rating   Vicinity   Rating    Vicinity      Rating   Vicinity   Rating
Genk        4          Brugge   3,7       Vilvoorde       3,8        Mons      3,8

This should be in 2 columns:

Vicinity    Rating
Genk          4
Brugge       3,7
Vilvoorde    3,8
Mons          3,8

I can't find the code/option to merge these 😞

This is the code that I have now:

let
Source = Json.Document(Web.Contents("xxx")),
result = Source[result],
#"Converted to Table" = Record.ToTable(result),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "rating" or [Name] = "vicinity")),
#"Appended Query" = Table.Combine({#"Filtered Rows", Brugge, Hannut, Mons, Couillet, Poincaré, Vilvoorde, Genk}),
#"Reversed Rows" = Table.ReverseRows(#"Appended Query"),
#"Transposed Table" = Table.Transpose(#"Reversed Rows"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"vicinity", type text}, {"rating", type number}, {"vicinity_1", type text}, {"rating_2", type number}, {"vicinity_3", type text}, {"rating_4", type number}, {"vicinity_5", type text}, {"rating_6", Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"rating", type text}, {"rating_2", type text}, {"rating_4", type text}, {"rating_6", type text}, {"rating_8", type text}, {"rating_10", type text}, {"rating_12", type text}, {"rating_14", type text}})
in
#"Changed Type1"


Kind regards,
Jeffrey

[ChandeepChhabra · Accepted solution]

Hi @jeffrey759

Try the following code. Note that I have converted your data into a table (named vicinity) in Excel and wrote PQ code there

let
Source = Excel.CurrentWorkbook(){[Name="Vicinity"]}[Content],
#"Transposed Table" = Table.Transpose(Source),
#"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 1, 1),
#"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 2), type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Calculated Modulo", "Custom", each if [Index] = 0 then [Column1] else null ),
#"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Index] = 1)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Vicinity"}, {"Custom", "Rating"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Rating", type text}, {"Vicinity", type text}})
in
#"Changed Type"

Here is the snapshot of ther result

You can download my excel (powerquery) file here

thanks

[v-xjiin-msft]

@jeffrey759

I suppose your sample data is like this:

Unpivot it with Power Query script:

let
    Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJPzcsGUiZA7FRUmp6eCmQY65gDybDMnLL8/KIUiIiFApDyzc8rhvCUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, Vicinity.0 = _t, Rating.0 = _t, Vicinity.1 = _t, Rating.1 = _t, Vicinity.2 = _t, Rating.2 = _t, Vicinity.3 = _t, Rating.3 = _t]),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJPzcsGUiZA7FRUmp6eCmQY65gDybDMnLL8/KIUiIiFApDyzc8rhvCUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, Vicinity.0 = _t, Rating.0 = _t, Vicinity.1 = _t, Rating.1 = _t, Vicinity.2 = _t, Rating.2 = _t, Vicinity.3 = _t, Rating.3 = _t]),
    #"Removed Columns1" = Table.RemoveColumns(Source,{"Vicinity.0", "Vicinity.1", "Vicinity.2", "Vicinity.3"}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Item"}, "Attribute", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Unpivoted Columns1", "Merge", each [Item] & List.Last(Text.Split([Attribute],"."))),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Vicinity.0", type text}, {"Rating.0", Int64.Type}, {"Vicinity.1", type text}, {"Rating.1", type number}, {"Vicinity.2", type text}, {"Rating.2", type number}, {"Vicinity.3", type text}, {"Rating.3", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Rating.0", "Rating.1", "Rating.2", "Rating.3"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Item"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Merge", each [Item] & List.Last(Text.Split([Attribute],"."))),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Merge"},#"Added Custom1",{"Merge"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Value"}, {"NewColumn.Value"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded NewColumn",{"Attribute", "Merge"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"NewColumn.Value", "Rating"}, {"Value", "Vicinity"}})
in
    #"Renamed Columns"

The Result shows like this:

Refence: https://community.powerbi.com/t5/Desktop/Unpivot-Multiple-Sections-of-Data/td-p/20942 

Thanks,
Xi Jin.

[jeffrey759]

Hi,

Already thank you but I can't get it to work (I'm quite new to this).
The goal is to have a file, that updates through an API from Google Business.

If i try the codes, I keep getting an error 😞

This is what I actually got:

And this is my full code:

let
Source = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/place/details/json?placeid=ChIJx-b_oGbmw0cR0k3Q_aWKvNE&key=AIza...")),
result = Source[result],
#"Converted to Table" = Record.ToTable(result),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "rating" or [Name] = "vicinity")),
#"Appended Query" = Table.Combine({#"Filtered Rows", Brugge, Hannut, Mons, Couillet, Poincaré, Vilvoorde, Genk}),
#"Reversed Rows" = Table.ReverseRows(#"Appended Query"),
#"Transposed Table" = Table.Transpose(#"Reversed Rows"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"vicinity", type text}, {"rating", type number}, {"vicinity_1", type text}, {"rating_2", type number}, {"vicinity_3", type text}, {"rating_4", type number}, {"vicinity_5", type text}, {"rating_6", Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"rating", type text}, {"rating_2", type text}, {"rating_4", type text}, {"rating_6", type text}, {"rating_8", type text}, {"rating_10", type text}, {"rating_12", type text}, {"rating_14", type text}})
in
#"Changed Type1"

[v-xjiin-msft · Accepted solution]

@jeffrey759

Please refer to my sample data screenshot. The column name format in my smaple data is like: Vicinity.0, Rating.0 and Vicinity.1, Rating.1 and so on. So that I can know which rating belonds to which vicinity. Also, I have a group column called Item. With this if there exists multiple groups, we can know which vicinities or ratings are in the same row.

Thereby, in your scenario, your column names are like Vicinity, Vicinity_1, Rating_2 ... It is hard to know which rating belongs to which vicinity. Please format your sample data first by renaming the column names. And consider if it is necessary to add a group column.

Thanks,
Xi Jin.

[ChandeepChhabra · Accepted solution]

Hi @jeffrey759

Try the following code. Note that I have converted your data into a table (named vicinity) in Excel and wrote PQ code there

let
Source = Excel.CurrentWorkbook(){[Name="Vicinity"]}[Content],
#"Transposed Table" = Table.Transpose(Source),
#"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 1, 1),
#"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 2), type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Calculated Modulo", "Custom", each if [Index] = 0 then [Column1] else null ),
#"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Index] = 1)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Vicinity"}, {"Custom", "Rating"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Rating", type text}, {"Vicinity", type text}})
in
#"Changed Type"

Here is the snapshot of ther result

You can download my excel (powerquery) file here

thanks