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

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"