Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I am facing an issue and not a great list and records. Need some help here.
I have a table to multiple columns. I want to check columns Standby 1 to Standby 8 and display the names of columns as text separate by a semicolon or comma in new columns for which the values are not null.
For Example Below:
Name | Number | SKU Name | Standby 1 | Standby 2 | Standby 3 | Standby 4 | Standby 5 | Standby 6 | Standby 7 | Standby 8 | |
PS 1 | 555 | PSK 1 | null | null | null | null | null | 5 | 5.1 | null | |
PS 2 | 123 | PSK 2 | null | null | 4.5 | 6 | 7 | null | null | null | |
PS 3 | 543 | PSK 3 | null | null | null | null | null | null | null | null | |
PS 4 | 234 | PSK 4 | 5.5 | 6 | null | null | null | null | null | null | |
PS 5 | 341 | PSK 5 | 4 | null | null | null | null | null | null | null | |
| |||||||||||
Expected Result with Additional Column | |||||||||||
Name | Number | SKU Name | Standby 1 | Standby 2 | Standby 3 | Standby 4 | Standby 5 | Standby 6 | Standby 7 | Standby 8 | Standby Columns (New Column) |
PS 1 | 555 | PSK 1 | null | null | null | null | null | 5 | 5.1 | null | Standby 6, Standby 7 |
PS 2 | 123 | PSK 2 | null | null | 4.5 | 6 | 7 | null | null | null | Standby 3, Standby 4, Standby 5 |
PS 3 | 543 | PSK 3 | null | null | null | null | null | null | null | null | null |
PS 4 | 234 | PSK 4 | 5.5 | 6 | null | null | null | null | null | null | Standby 1, Standby 2 |
PS 5 | 341 | PSK 5 | 4 | null | null | null | null | null | null | null | Standby 1 |
Solved! Go to Solution.
Hi
another solution
Add column :
Text.Combine(
Table.SelectRows(
Record.ToTable(_),
each [Value]<>null and Text.StartsWith([Name], "Standby"))[Name],
", ")
Stéphane
Text.Combine(
List.Transform(
Table.SelectRows(
Record.ToTable(_),
each [Value]<>null and List.Contains( Your_List, [Name]))[Name],
each Text.BetweenDelimiters(_, "_", " (", 1, 0)),
", ")
Stéphane
Hi @pranay_singh, check also this:
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCghWMFTSUTI1NQWSAcHeYF5eaU4OAQqk3FQPrjhWB2yUEZBvaGQMNcoIQ5eJHkifGRCbYzcYag7IBFMTmDnGxDkJt3EmQL6RsQnUOBOw02EOIdEokDZjE0OoUSCeCRnOigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Number = _t, #"SKU Name" = _t, #"Standby 1" = _t, #"Standby 2" = _t, #"Standby 3" = _t, #"Standby 4" = _t, #"Standby 5" = _t, #"Standby 6" = _t, #"Standby 7" = _t, #"Standby 8" = _t]),
// You can probably delete this step
RaplaceValues = Table.TransformColumns(Source, {}, each if List.Contains({"null", ""}, _) then null else _),
Ad_StandByColumns = Table.AddColumn(RaplaceValues, "Standby Columns", each
[ a = List.Select(Record.FieldNames(_), (x)=> Record.Field(_, x) <> null and Text.StartsWith(x, "Standby", Comparer.OrdinalIgnoreCase)),
b = if List.IsEmpty(a) then null else Text.Combine(a, "#(lf)")
][b], type text )
in
Ad_StandByColumns
Hi Thank you for this, It does work but it makes the query very slow.
Hi again, try this one and let me know please (it is significantly faster):
You can probably delete this step:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCghWMFTSUTI1NQWSAcHeYB4aAkmZ6oElYnXAWoyAbEMjY6gWI7hKEz2QWjMgNkcYANUDUm1qAtNjjGkNqnITINvI2ASq3ATsBJjhOLSApI1NDKFaQDwTHFbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Number = _t, #"SKU Name" = _t, #"Standby 1" = _t, #"Standby 2" = _t, #"Standby 3" = _t, #"Standby 4" = _t, #"Standby 5" = _t, #"Standby 6" = _t, #"Standby 7" = _t, #"Standby 8" = _t]),
// You can probably delete this step
ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
AddedIndex = Table.AddIndexColumn(ReplaceBlankToNull, "Index", 0, 1, Int64.Type),
UnpivotedColumns = Table.Unpivot(AddedIndex, List.Select(Table.ColumnNames(AddedIndex), each Text.StartsWith(_, "Standby", Comparer.OrdinalIgnoreCase)), "Attribute", "Value"),
GroupedRows = Table.Group(UnpivotedColumns, {"Index"}, {{"Standby Columns", each Text.Combine([Attribute], ", "), type text}}),
MergedQueries = Table.NestedJoin(AddedIndex, {"Index"}, GroupedRows, {"Index"}, "GroupedRows", JoinKind.LeftOuter),
ExpandedGroupedRows = Table.ExpandTableColumn(MergedQueries, "GroupedRows", {"Standby Columns"}, {"Standby Columns"}),
RemovedColumns = Table.RemoveColumns(ExpandedGroupedRows,{"Index"})
in
RemovedColumns
Hi,
It appears that you want to display the names of columns with null values in the "StandBy Columns" column.
Below is the code for reference where I retrieve data from Microsoft Dataverse:
let
Source = CommonDataService.Database(""), // Your Datasource
dbo_account = Source{[Schema="dbo",Item="account"]}[Data],
SelectedColumns = Table.SelectColumns(dbo_account, {"name", "StandBy1", "StandBy2"}),
StandByColumns = Table.AddColumn(SelectedColumns, "StandBy Columns", each
Text.Combine(
List.Select(
List.RemoveItems(Table.ColumnNames(SelectedColumns), {"name"}), //To remove Unwanted Columns
(col) => Record.Field(_, col) = null
),
", "
)
)
in
StandByColumns
Note: Edit & paste this code in Advanced Editor of Power Query.
Hope this helps.
Hi,
Actually, I want to display the opposite. I want to just select the specific columns and display the name of the columns with NON null values.
Hi
another solution
Add column :
Text.Combine(
Table.SelectRows(
Record.ToTable(_),
each [Value]<>null and Text.StartsWith([Name], "Standby"))[Name],
", ")
Stéphane
Apologies, but one more question. If using Text.StartsWith. Is it possible to add that it only extracts a part of the text for each column Name.
For Example my Column names always are
_Standby_Nameofphase1 (Phase)
_Standby_Nameofphase2 (Phase)
_Standby_Nameofphase3 (Phase)
So that when I am combine and getting the names of the headers for all non null values. It gets the name and then extracts the text after 2nd "_" (underscore) and before (Phase)
So it will
Nameofphase1,Nameofphase2,Nameofphase3
Text.Combine(
List.Transform(
Table.SelectRows(
Record.ToTable(_),
each [Value]<>null and List.Contains( Your_List, [Name]))[Name],
each Text.BetweenDelimiters(_, "_", " (", 1, 0)),
", ")
Stéphane
Thank you so much! This works.
Hi,
Thank you so much for this. Is there way to not use the name logic.
Like not mentioning Text starts with and use it by selecting the set of columns in a record or list.
Yes, with List.Contains
=Text.Combine(
Table.SelectRows(
Record.ToTable(_),
each [Value]<>null and List.Contains({"Standby 1", "Standby 7"}, [Name]))[Name],
", ")
Stéphane
Hello @pranay_singh ,
If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.
If you have any questions, please feel free to get in touch.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY5LCsAgDAWvUlxnk197iG4El+L9r9EkxJa2CCoJbyax91LbhgWKqtpb2xnd53ikEMGAUMhqJE6FblLA2d3u8QxIx2mV6fB/zRsXq4klcYkvzOELxWMWTMU7WawYFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Number = _t, #"SKU Name" = _t, #"Standby 1" = _t, #"Standby 2" = _t, #"Standby 3" = _t, #"Standby 4" = _t, #"Standby 5" = _t, #"Standby 6" = _t, #"Standby 7" = _t, #"Standby 8" = _t]),
Replaced = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Standby 1", "Standby 2", "Standby 3", "Standby 4", "Standby 5", "Standby 6", "Standby 7", "Standby 8"}),
AddCustomColumn = Table.AddColumn(Replaced, "Null Columns", each
let
currentRow = _,
columns = {"Standby 1", "Standby 2", "Standby 3", "Standby 4", "Standby 5", "Standby 6", "Standby 7", "Standby 8"},
nullColumns = List.Select(columns, each Record.Field(currentRow, _) <> null)
in
Text.Combine(nullColumns, ", ")
)
in
AddCustomColumn
Best regards from Germany
Manuel Bolz
🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github
let
Source = your_table,
col_names = List.Buffer(List.LastN(Table.ColumnNames(Source), 8)),
selection = (lst) =>
[nulls = List.PositionOf(lst, null, Occurrence.All, (x, y) => x <> y),
names = List.Transform(nulls, (x) => col_names{x}),
value = Text.Combine(names, ", ")][value],
add_col = Table.AddColumn(Source, "SB columns", (x) => selection(List.LastN(Record.FieldValues(x), 8)))
in
add_col
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
67 | |
61 | |
23 | |
17 | |
13 |