Trying to combine multiple columns but keep the individual rows separated.

[caboos55]

Hello, I am trying to combine a total of 6 columns together, but keep the individual rows separated. To have an idea of the project I am working on, I have a node of sensors collecting data. I have multiple colums for each sensor and state. On powerBI I would like to have a singlular column for all of the individual sensors and their sensor states while having the inputs to be separated. Here is an image of part of the table to get an idea. If it were to have the entirety it would alternate from sensorFour, stateFour, sensorFive, stateFive, sensorSix, stateSix, ReadTime.  My hopes are to have columns for ID, Location, SensorID (combination of sensor columns), sensorState(combination of state columns), and readtime.

[latimeria · Accepted solution]

Hi @caboos55 ,

You can try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXI3sNB1AjEMdYGEW2lODpAyQmIbw9mxOtFKRqRrMSZdiwnpWkxJ12KGocUjMSdNIb9IwSe1uBimFU3MGEMsNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, location = _t, #"sensor One" = _t, #"state One" = _t, #"sensor Two" = _t, #"state Two" = _t, #"sensor Three" = _t, #"state Three" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"id", "location"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Filtered Rows sensor" = Table.SelectRows(#"Split Column by Delimiter", each Text.StartsWith([Attribute.1], "sensor")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows sensor",{{"Attribute.1", "sensorid"}, {"Value", "sensorIDValue"}, {"Attribute.2", "Nb"}}),
    #"Filtered Rows state" = Table.SelectRows(#"Split Column by Delimiter", each Text.StartsWith([Attribute.1], "state")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows state",{{"Attribute.1", "stateID"}, {"Value", "stateIDValue"}, {"Attribute.2", "Nb"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns1", {"id", "location", "Nb"}, #"Renamed Columns", {"id", "location", "Nb"}, "Renamed Columns1", JoinKind.LeftOuter),
    #"Expanded Renamed Columns1" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns1", {"sensorIDValue"}, {"sensorIDValue"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Renamed Columns1",{"stateID"})
in
    #"Removed Columns"