Read ini files with PowerBI and keep sections

Question

I would like to create a PowerBI dashboard using a large number of .ini files. The .ini files are located in an Azure blob storage and are read in via the function integrated in PowerBI. This works fine so far, but the content of the .ini file is read in line by line. I separate simple key-value pairs into two columns with a PowerBI function using the equals sign. However, the .ini file is divided into two sections with [tier1] and [tier2], which I can no longer distinguish later by reading in PowerBI line by line, as I only have one set of key-value pairs without assignment. How can I read in the key-value pairs with the information in which section they were in the .ini file?

1. The .ini looks like this and i want the sections [site], [tier1] and [tier2] as an adition to the key

2. This is the table i created using standart PowerBI import form the blob storage and the ideal solution would be an extra column with the specific section the key was written in the .ini file

3. This photo shows the table directly after the azure blob storage import before the conversion two the second photo was done by PowerBI

Answer 1

Try this:

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("..\example.ini"), null, null, 65001)}),
    #"Filtered Rows" = Table.SelectRows(Source, each [Column1] <> null and [Column1] <> ""),
    #"Trimmed Text" = Table.TransformColumns(#"Filtered Rows",{{"Column1", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Section", each if Text.StartsWith([Column1], "[") then Text.Remove([Column1], {"[", "]"}) else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Section"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each not Text.StartsWith([Column1], "[")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows1", "Column1", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Key", "Value"}),
    #"Trimmed Text1" = Table.TransformColumns(#"Split Column by Delimiter",{{"Key", Text.Trim, type text}, {"Value", Text.Trim, type text}, {"Section", Text.Trim, type text}})
in
    #"Trimmed Text1"

Which will give you a table like:

Key	Value	Section