User Profile
artpil
Resolver II
Joined 6 years ago
User Widgets
Contributions
Re: Countif and Combine
Hi, Link to the workbook was deleted. My idea is to sort the data by supplier, job number and date and group by supplier and job number and Text.Combine on Job Code. let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUfLKTwpKTQMygksLIAygiHN+SqpSrE60kpGBkZGugYWuoaWCobGVkQVQ2tHJ2dDIGMgwNzI2NALS7hgqjXCodMZQaUi0mQYQlc5OjgiVxtjMNLDEoRLDTAMLiEonZ0wzYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type datetime}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"SupRef", Order.Ascending}, {"JobRef", Order.Ascending}, {"Date", Order.Ascending}}), #"Inserted Merged Column" = Table.AddColumn(#"Sorted Rows", "Unique", each Text.Combine({[SupRef], [JobRef]}, ""), type text), Codes = Table.Group(#"Inserted Merged Column", {"JobRef", "SupRef"}, {{"Count", each Text.Combine( [JobCode],">"), type nullable text}}), Custom1 = #"Inserted Merged Column", #"Merged Queries" = Table.NestedJoin(Custom1, {"SupRef", "JobRef"}, Codes, {"SupRef", "JobRef"}, "Custom1", JoinKind.LeftOuter), #"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Custom1", {"Count"}, {"Count"}) in #"Expanded Custom1" Hope this helps, Artur813Views0likes0CommentsRe: Power Query - customer column if statement with list.contains and wildcard?
Hi, I created very similar solution to Anonymous but I think more customizable. First I created query with a table with two columns: issue type and list of terms to match issue type. Table name is Issues let Source = Table.FromRecords({[Issue="transportation", Terms={"FEDEX", "TACK","TRACKING", "NOT DELIVERED", "NO DELIVERY"}],[Issue="other", Terms={"FEDEX", "TACK","TRACKING", "NOT DELIVERED", "NO DELIVERY"}]}) in Source then I added column if text matches transportation problems. Column ListOfIssues contains list of matching issue types from table Issues. let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVJLbxoxEP4rI3pppGjFbgpEvVWBqpFSFUGlHAhSjW2yLl7Pyg8e/74zdkJROfSwu9b68/car1aD3moRNHjd4V7D1mMHy+9LEE7B9GF5CwfQ/Hj6hBZ9BK0AHeB2C0byuRBR7gC9Ikxv4T9sheyK64pqsL5dDaZGgcNIO1Ib4jNRd2BNiHRuoy0eKniMcBDhHaHAOAinwLiIIC0GjSkWygrmJavSKslY3Bm31y6iP2XBxWz+9OVh9mMxnS0+QzO5b+7qyThv1UW9MyEY95oT1aCP0QvWjK2GDR4z8ptQIKAXcideNe2IyGkPgR1tWN2SU09e90bAV61mR9iQR6pGCpfzWpQiami5KD5NyhUwLZIOtexRGUpQQdb72ZoApuutcKUMpjBO2qRKIcQqpMTk4ksaDptJOFcdWtN3lP9czeXgMHmYPnIAt4MTpizmkrV5MX/Ldx7k0ZaGPtb3d3UzhhsazoUp5mU3VAKFqEefsnjPXSoTJDu8skHAqnpL+e9doD0CNlkzVBligdYfhsNxM6LXqFletMHTKTXTLywD4xGxfrCmP0sL9TuF+Pde5Oq8Ipw9VfBMUxHk6ddgvf4D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Header Notes" = _t]), Separators = let ls=List.Range({31..47},1) & List.Range({57..64},1) & List.Range({90..96},1)& List.Range({123..126},1), binaryData = #binary(ls), textFormat = BinaryFormat.Text( List.Count(ls), TextEncoding.Ascii ), textToSplit= textFormat(binaryData) in textToSplit, Transportation = Table.AddColumn(Source, "Transportation", each List.NonNullCount( List.Intersect({Text.Split(Text.Upper([Header Notes])," "),Issues[Terms]{0}}))), LstOfIssues = Table.AddColumn(Transportation, "ListOfIssues", each List.Accumulate(Table.ToRecords(Issues),{},(cumulator,IssuesRec)=> if List.NonNullCount( List.Distinct( List.Intersect({IssuesRec[Terms],List.RemoveNulls(List.RemoveMatchingItems(Text.SplitAny(Text.Upper([Header Notes]),Separators),{"","NULL"}))})))>0 then cumulator & {Record.Field(IssuesRec,"Issue")} else cumulator )) in LstOfIssues Hope this helps, Artur7.9KViews0likes0CommentsRe: Expanding ListColumns within Nested Tables
Hi, So you probably want something like this: let Source = Table.FromRecords( { [Name = "Bob", Color = "Blue"], [Name = "Jim", Color = "Yellow"], [Name = "Paul", Color = "Green"] } ), #"AddTab;e"= Table.AddColumn (Source, "TypeID", each Table.FromRecords( { [Type= "Small", ID = {1,2,3}], [Type = "Medium", ID = {2,4,6}], [Type = "Large", ID = {1,3,6}] } ) ), TransformColumn = Table.TransformColumns(#"AddTab;e",{"TypeID", each Table.ExpandListColumn(_, "ID")}) in TransformColumn Hope it helps, Artur2.7KViews0likes1CommentRe: Change referenced table based on cell value
Hi, Here's example of possible solution. Sorry but I don't have to much time to clean it up. I created 3 queries query 1 named "Employees, 2022" let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUQpJLEpPLQlLzEk1MjAyUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Users = _t, TargetColumn = _t]) in Source query 2 named "Employees, 2021" let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnPVdJRCkksSk8tCUvMSTUyMDJUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Users = _t, TargetColumn = _t]) in Source and query with the solution let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXLNLcjJr0xNLdZRMDIwMlKK1YlWCsnPxZAxVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"AUTHOR_NAME]" = _t, TabeFrom = _t]), #"Added Custom" = Table.AddColumn(Source, "Table", each let tableF= [TabeFrom] in Table.SelectRows(Record.ToTable(#shared), each [Name]=tableF)), #"Expanded Table" = Table.ExpandTableColumn(#"Added Custom", "Table", {"Value"}, {"Table"}), #"Added Custom1" = Table.AddColumn(#"Expanded Table", "Output", each let auth=[#"AUTHOR_NAME]"] in Table.SelectRows([Table], each [Users]=auth)), #"Expanded Output" = Table.ExpandTableColumn(#"Added Custom1", "Output", {"TargetColumn"}, {"TargetColumn"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Output",{"Table"}) in #"Removed Columns" I'm using Record.ToTable(#shared) statement to create table with tables. Then I'm selecting correct table using Table.SelectRows. Hope this helps. Artur1.9KViews1like0CommentsRe: Multiple Rows based on Unique ID
HI, Here's one posible solution - sql like aproach. Paste the code to advanced editor. let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs3LLCxNVfB0UdJRikxNLFJQitWJVjI0MgbyjQwMDMFcE1MzCNdCKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), OriginValues = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUfJLLVeK1YGwQ4tTU+Ac/xwgOxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TempId = _t, Origin = _t]), #"Added Custom" = Table.AddColumn(#"Promoted Headers", "TempId", each "a"), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"TempId"}, OriginValues, {"TempId"}, "Added Custom", JoinKind.LeftOuter), #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Origin"}, {"Origin"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom",{"TempId"}) in #"Removed Columns" Hope this helps, Artur1.4KViews2likes0CommentsRe: AddIndexColumn based on unique values in columns
Hi, Here's the code that counts distinct cars by sales person in a state. let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZHNCoMwEIRfRXIWTCP+NNeKhx5KqV6KeAi4B6m6ENOWvn33UNtSjaQQNiSZj5khVcUKowwwnxWqg/EIesSBTjulaWb0VLY9sNqv2AHu3hn1he5L7GnmqBvaBA94EgguhMcTSSuMnfWpjFIpUmf9Vkax5M76DZdRQsivfo9q+KvACyjxgUbNOoRchrNMdg9LaTtgab0Syla8MHADe5HFXBPjHmwiTjCoa2dWouUd6rZR7y9czPXt4gJ8HOon", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), BuffZipList1 = List.Buffer(List.Zip({PromotedHeaders[State],PromotedHeaders[SalesPerson],PromotedHeaders[Car]})), #"Added Custom" = Table.AddColumn(PromotedHeaders, "UniqueCarsSold", each List.Count(List.Distinct(List.Transform(List.Select(BuffZipList1, (x)=>x{0}=[State] and x{1}=[SalesPerson]), each _{2})))) in #"Added Custom" Paste the code to advanced editor. What the code does: in step BuffZipList1 list of lists based on columns State, SalesPerson and Car is being created, in step #"Added Custom" first is filtered ( List.Select statement), then list is truncated to unique values and finally items are being counted. Hope this helps. Artur1.4KViews1like0CommentsRe: Doubled values in query
Hi, Ask your colleague to input his credentials on your computer. If the output will be correct then you should analyse sql if the views providing data have some restrictions based on user login. If not, try to just load data from both systems and compare tables one to one. It is always tricky to find what's the reason for cases like this. Artur464Views0likes1CommentRe: Select columns with specified data type
If you would like to only select the columns with the type of percentage you can't use Table.ColumsOfType function because it treats all number kinds (whole numbers, decimals, percentage, currency) as numbers. IF for example if you want to select only currencies, you would have to Table.ScemaFunction. Artur5KViews0likes0Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.